Alex Rivera | Logout

How to restore a database from C#

Asked 2009-09-23T15:14:32.063
16

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

Edit
Report

1 Answer

6

You are adding RelocateFile options based on the database name, that is incorrect. You should add them based on the logical file name for each file relocated. Use Restore.ReadFileList to retrieve the list of logical file names.

answered 2009-09-23T15:22:46.010

Your Answer