Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to restore a database from C#

I have a SQL 2008 DB. I am running a form that backs that DB up, then tries to update it. If the update fails the idea is to restore that backup. Here is the code I am using to restore the backup.

public void RestoreDatabase(String databaseName, String backUpFile, String serverName, String userName, String password)
{
    Restore sqlRestore = new Restore();
    BackupDeviceItem deviceItem = new BackupDeviceItem(backUpFile, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    sqlRestore.Action = RestoreActionType.Database;

    string logFile = System.IO.Path.GetDirectoryName(backUpFile);
    logFile = System.IO.Path.Combine(logFile, databaseName + "_Log.ldf");

    string dataFile = System.IO.Path.GetDirectoryName(backUpFile);
    dataFile = System.IO.Path.Combine(dataFile, databaseName + ".mdf");

    Database db = sqlServer.Databases[databaseName];
    RelocateFile rf = new RelocateFile(databaseName, dataFile);
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFile));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFile));
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

The issue seems to be that the file names I pick are different from the online DB. I basically want to replace the database on the server with the backup. I get an exception when I call SqlRestore.

The main exception says

{"Restore failed for Server 'localhost'. "}

Digging into the inner exceptions shows these errors

An exception occurred while executing a Transact-SQL statement or batch.

and then

Logical file 'DB' is not part of database 'DB'. Use RESTORE FILELISTONLY to list the logical file names.\r\nRESTORE DATABASE is terminating abnormally.

I assume there is some way to tell this to just use replace the existing DB as is.

I use this bit of code to get the file path of the DB to have a directory to dump the backup. Maybe this could be used to get the file names to recreate.

public string GetDBFilePath(String databaseName, String userName, String password, String serverName)
{
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    Database db = sqlServer.Databases[databaseName];
    return sqlServer.Databases[databaseName].PrimaryFilePath;
}
like image 606
Anthony D Avatar asked Sep 23 '09 15:09

Anthony D


People also ask

How do you restore a database?

In the left navigation bar, right-click on Databases and then click Restore Database. In the Source section, select Device and click the button with three dots. In the pop up window that opens, click Add and browse for your backup file. Click OK.

How do you backup and restore SQL database in C#?

In the code above you can change the connection string corresponding to your database. Now run the application and select the server name and database name to restore the database backup file. Now click on the "Restore" Button and select the backup file location from the disk.

How do I restore a .SQL file?

Restore a backup Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Right-click the Databases node in Object Explorer and select Restore Database.... Select Device:, and then select the ellipses (...) to locate your backup file. Select Add and navigate to where your .


2 Answers

I changed my back up and restore functions to look like this:

public void BackupDatabase(SqlConnectionStringBuilder csb, string destinationPath)
{
    ServerConnection connection = new ServerConnection(csb.DataSource, csb.UserID, csb.Password);
    Server sqlServer = new Server(connection);

    Backup bkpDatabase = new Backup();
    bkpDatabase.Action = BackupActionType.Database;
    bkpDatabase.Database = csb.InitialCatalog;
    BackupDeviceItem bkpDevice = new BackupDeviceItem(destinationPath, DeviceType.File);
    bkpDatabase.Devices.Add(bkpDevice);
    bkpDatabase.SqlBackup(sqlServer);
    connection.Disconnect();

}

public void RestoreDatabase(String databaseName, String backUpFile, String serverName, String userName, String password)
{
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    Restore rstDatabase = new Restore();
    rstDatabase.Action = RestoreActionType.Database;
    rstDatabase.Database = databaseName;
    BackupDeviceItem bkpDevice = new BackupDeviceItem(backUpFile, DeviceType.File);
    rstDatabase.Devices.Add(bkpDevice);
    rstDatabase.ReplaceDatabase = true;
    rstDatabase.SqlRestore(sqlServer);
}

That way they just use whatever files are there. There are no longer and directives to relocate files.

like image 97
Anthony D Avatar answered Sep 22 '22 09:09

Anthony D


Thanks Remus for your answer!

I have modified

sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation)); 

these two lines to

System.Data.DataTable logicalRestoreFiles = sqlRestore.ReadFileList(sqlServer);
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[0][0].ToString(), dataFileLocation));
sqlRestore.RelocateFiles.Add(new RelocateFile(logicalRestoreFiles.Rows[1][0].ToString(), logFileLocation));

and my code is running successfully.

Thanks for the support!

like image 29
Pallavi Avatar answered Sep 20 '22 09:09

Pallavi