如何从 C# 恢复数据库

How to restore a database from C#

提问by Anthony D

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.

我有一个 SQL 2008 数据库。我正在运行一个支持该数据库的表单，然后尝试更新它。如果更新失败，则想法是恢复该备份。这是我用来恢复备份的代码。

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.

问题似乎是我选择的文件名与在线数据库不同。我基本上想用备份替换服务器上的数据库。调用 SqlRestore 时出现异常。

The main exception says

主要的例外说

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

{“服务器'localhost'的恢复失败。”}

Digging into the inner exceptions shows these errors

深入研究内部异常会显示这些错误

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

执行 Transact-SQL 语句或批处理时发生异常。

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.

逻辑文件“DB”不是数据库“DB”的一部分。使用 RESTORE FILELISTONLY 列出逻辑文件名。\r\nRESTORE DATABASE 异常终止。

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;
}

采纳答案by Anthony D

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.

这样他们就可以使用那里的任何文件。不再有用于重定位文件的指令。

回答by Remus Rusanu

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

您正在RelocateFile根据数据库名称添加选项，这是不正确的。您应该根据每个重新定位的文件的逻辑文件名添加它们。使用Restore.ReadFileList检索逻辑文件名列表。

回答by Pallavi

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!

感谢您的支持！