c# - 如何从 C# 恢复数据库

Question

我有一个 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();
}

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

主要异常说

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

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

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

然后

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

我假设有一些方法可以告诉它按原样替换现有的数据库。

我使用这段代码获取数据库的文件路径，以便有一个目录来转储备份。也许这可以用来获取要重新创建的文件名。

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

Answer 1

我将备份和恢复功能更改为如下所示:

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

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