0

I want to restore a (not yet existing) database from a .bak file into a remote database server via a C# (.Net 4.0) program.

I know it is not possible via an SQL script to do so, because the .bak file needs to be located on the SQL Server machine. Is it possible via C# though?

Basically i want this:

public bool RestoreDatabase(FileInfo backupFile, string connectionString, string dbName) 
{
    // Magically Restore Database
    // Throw Exception on error (Db already exists, file not found, etc)
}

Before i invest hours of programming and investigation, i want to know if it is technically possible, or if i have to transfer the .bak file to the SQL server.

EDIT: The context is, that i want to minimize "manual" work that needs to be done in my current project. Currently, when installing the software in a new environment, someone has to manually install the databases by copying the .bak file to the SQL Server, opening SQL Server Manager and restoring the database there. 2 databases are needed, and those MIGHT be placed on 2 different SQL Servers.

My idea was to run 1 programm on ANY system in the same network as the SQL Servers, entering SQL Login credentials, and restoring the databases from the one system, without any network drive. Since this would again mean manual configuration (copy .bak file to network drive, enable SQL server to access network drive [actually then you can just copy the file directly to the SQL server]), i want to avoid it.

EDIT2: Due to different context-related issues, i cannot export the database as SQL/DACPAC/Snapshot. It has to be .bak sadly.

Improve this question
10
  • 1
    Well, the SQL Server instance should be able to access the backup file somehow, locally or via a network share. Commented Mar 5, 2015 at 9:48
  • Couldn't the backup data be fed to the SQL Server via any Framework? Instead of giving the file to the server and saying "here, eat this" :-) Commented Mar 5, 2015 at 9:51
  • 1
    @AlexanderMills are you asking how to deploy a database as part of a setup package? This isn't related to the question title. You can simply execute the restore command with a SqlCommand, either during setup with a custom action, an initialization script or in an initialization step in your application. Or, you can package the database as a DACPAC or recreate it using a script. This has nothing to do with remote restores. Commented Mar 5, 2015 at 10:09
  • 1
    A network share I think you misunderstand what backup and restore do. They don't copy new data over the old and the files aren't simple copies of the existing database files. You can't just feed them to the server, the server needs access to them so it can pick the right data, process it and do the restore operation without freezing the database. Commented Mar 5, 2015 at 10:20
  • 1
    The real answer is - you are doing it wrong. You should check Data-Tier applications. They cover the scenario you mention - they even allow deployment to Azure's SQL Database which can't have access to file shares Commented Mar 5, 2015 at 10:26

2 Answers 2

Reset to default
1

You asked "Is it possible via C# though?".

The answer is no, it isn't possible via C#.

As @Mithrandir says in the comments, the computer running SQL Server must be able to access the physical backup file somehow. That means the file either needs to be located on that computer somewhere, or it must reside on a file share to which the computer has access.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Alright, a simple "no" answers the question. I was hoping there would be some neat workaround, but i guess i have to think of another way to do it.
@AlexanderMills Yeah, it's unfortunate but that seems to be the only way. We had to do something similar for a project at work and in the end had to put the backup files on a share that the SQL Server computer could access.
@AlexanderMills there are ways, you are just looking at it the wrong way. There are multiple techniques for deploying databases, each with its own advantages and drawbacks. Most of them (restore included) work through C# as well
@PanagiotisKanavos Could you post an answer detailing that? I would be very interested in seeing it.
1

Another option is to generate sql scripts that create the whole database even with initial data as INSERT statements. You do not need to transfer any BAK file then.

Improve this answer

4 Comments

Yes, i that came to my mind too. Sadly (i did not specify it in the question) i only have access to the finished .bak file, not the "source" database. I receive a .bak (or 2, as described above) and have to restore those as new databases in target SQL Server. And converting .bak to .sql is not possible as far as i know
What's wrong with restoring the database on your development PC and creating the scripts or dacpac from it?
I tried to keep the original question short and simple, so there are some minor bits missing, explaining why it is not possible. The tool should be used for 2 tasks. I give a "fresh" Database as .bak to the customer and restore it on their SQL server, and they give me their database as .bak sometimes for maintenance tasks. When customers send me their .bak, i have to manually put it in the development SQL Server, which i wanted to automize. If i restore the .bak locally and extract the dacpac/script, i can just directly add it to the SQL server. Which i don't want to do...
@VojtěchDohnal sadly not. It has to be "restore from .bak"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.