Restore MS SQL Database from path other than "c:\Program Files\Microsoft SQL\...\Backup"

Question

I want to reduce the effort of creating a database on the local machine by restoring an empty one instead of creating it via SQL.

This is my code so far (DataAccess.ApplicationDirectory is "C:\ProgramData\RC Vehicle Management\"):

 public static void CreateLocalDatabase () {
        const String emptyDatabaseFileName = "EmptyDatabase.bak";

        if(Directory.Exists(DataAccess.ApplicationDirectory) == false) {
            Directory.CreateDirectory(DataAccess.ApplicationDirectory);
        }
        File.WriteAllBytes(Path.Combine(DataAccess.ApplicationDirectory + emptyDatabaseFileName), Resources.RcVehicleManagement);

        using (SqlConnection sqlConnection = DataAccess.LocalMachineConnection()) {
            sqlConnection.Open();
            using (SqlCommand sqlCommand = new SqlCommand("RESTORE DATABASE [@localDatabaseName] " +
                                                          "FROM DISK = '@emptyDatabasePath' " + 
                                                          "WITH NOUNLOAD, REPLACE", sqlConnection)) {
                sqlCommand.Parameters.Add("@localDatabaseName", System.Data.SqlDbType.VarChar).Value = DataAccess.LocalDatabaseConnectionString.InitialCatalog;
                sqlCommand.Parameters.Add("@emptyDatabasePath", System.Data.SqlDbType.VarChar).Value = Path.Combine(DataAccess.ApplicationDirectory, emptyDatabaseFileName);

                sqlCommand.ExecuteNonQuery();
            }
        }
    }

But when executing, I get the following Exception (at "sqlCommand.ExecuteNonQuery();"):

Cannot open backup device 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\@emptyDatabasePath'.
Operating system error 2(failed to retrieve text for this error. Reason: 15105).
RESTORE DATABASE is terminating abnormally.

I have no idea why it wants to get the backup from "c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup", the path I declar would be "C:\ProgramData\RC Vehicle Management\EmptyDatabase.bak"

What do I do wrong? Or is it just not possible to restore from a path other than "c:\Program Files\Microsoft SQL...\Backup"?

Drasive · Accepted Answer · 2013-08-15 10:08:46Z

3

I found the problem, you can't use brackets or quotation marks around the parameters.

This works:

SqlCommand sqlCommand = new SqlCommand("RESTORE DATABASE @localDatabaseName " +
                                       "FROM DISK = @emptyDatabasePath " +
                                       "WITH NOUNLOAD, REPLACE", sqlConnection)

edited Aug 15, 2013 at 10:08

answered May 13, 2013 at 10:48

Drasive

3211 silver badge20 bronze badges

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

2 Comments

CodeCaster Over a year ago

That is very selective quoting. Right after it says that, you can read: "For more information, see "To restore files and filegroups to a new location," later in this topic. ".

Drasive Over a year ago

This was corrent, but I found the solution now.

Collectives™ on Stack Overflow

Restore MS SQL Database from path other than "c:\Program Files\Microsoft SQL\...\Backup"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related