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.

Alexander Mills
  • 277
  • 3
  • 18
  • 1
    Well, the SQL Server instance should be able to access the backup file somehow, locally or via a network share. – Mithrandir Mar 05 '15 at 09: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" :-) – Alexander Mills Mar 05 '15 at 09:51
  • It's doable, but imho this would mean to reimplement the "restore" command, more or less. – Mithrandir Mar 05 '15 at 09:56
  • 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. – Panagiotis Kanavos Mar 05 '15 at 10:09
  • @PanagiotisKanavos but the Restore command would not work remotely. Yes, you are right, i want to deploy a database in a kind of setup package. However, the setup would not be run on the SQL Server. – Alexander Mills Mar 05 '15 at 10:12
  • I know the question is how to do this with C#. But I will rather prefer Powershell to automate SQL Server Administrative task. This link should help you, you can fire Powershell scripts from C# as well. http://www.codeproject.com/Articles/110908/SQL-DB-Restore-using-PowerShell – Gaurav Sharma Mar 05 '15 at 10:19
  • 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. – Panagiotis Kanavos Mar 05 '15 at 10:20
  • Alright, i guess you are right. Since my question was if it is possible to do, it has been answered :-) – Alexander Mills Mar 05 '15 at 10:23
  • 1
    The real answer is - you are doing it wrong. You should check [Data-Tier applications](https://msdn.microsoft.com/en-us/library/ee210569.aspx). They cover the scenario you mention - they even allow deployment to Azure's SQL Database which can't have access to file shares – Panagiotis Kanavos Mar 05 '15 at 10:26
  • @PanagiotisKanavos i only receive .bak files as source. Exctracting an DAC or (as suggested below) an SQL script containing all Data is not possible for me. – Alexander Mills Mar 05 '15 at 10:33

2 Answers2

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.

Steven Rands
  • 5,160
  • 3
  • 27
  • 56
  • 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. – Alexander Mills Mar 05 '15 at 10:25
  • @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. – Steven Rands Mar 05 '15 at 10:26
  • @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 – Panagiotis Kanavos Mar 05 '15 at 10:26
  • @PanagiotisKanavos Could you post an answer detailing that? I would be very interested in seeing it. – Steven Rands Mar 05 '15 at 10:28
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.

Community
  • 1
  • 1
Vojtěch Dohnal
  • 7,867
  • 3
  • 43
  • 105
  • 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 – Alexander Mills Mar 05 '15 at 10:26
  • 1
    What's wrong with restoring the database on your development PC and creating the scripts or dacpac from it? – Panagiotis Kanavos Mar 05 '15 at 10:30
  • 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... – Alexander Mills Mar 05 '15 at 10:37
  • @VojtěchDohnal sadly not. It has to be "restore from .bak" – Alexander Mills Mar 05 '15 at 10:48