SMO:恢复到不同的数据库

Question

我已经阅读了十几个不同的博客，并通读了 msdn 示例，但它们对我不起作用。

最终我想做的是自动将数据库从我们的生产实例移动到我们的开发实例，或其他方向。

我采取的方法是:

1. 备份/恢复到临时数据库
2. 分离临时数据库
3. 复制mdf和ldf文件到另一个实例
4. 重新连接。

我卡在 1 上了，我不明白为什么。我读过的所有内容都声称这应该有效。

注意:我已将 dbName 设置为我要恢复到的数据库。我还设置了 restore.Database = dbName，其中 restore 是 smo 中的 Restore 类的一个实例命名空间。

mdf.LogicalFileName = dbName;
mdf.PhysicalFileName = String.Format(@"{0}\{1}.mdf", server.Information.MasterDBPath, dbName);
ldf.LogicalFileName = dbName + "_log";
ldf.PhysicalFileName = String.Format(@"{0}\{1}.ldf", server.Information.MasterDBPath, dbName);

restore.RelocateFiles.Add(mdf);
restore.RelocateFiles.Add(ldf);
restore.SqlRestore(server);

这是我遇到的异常:

The file 'D:\MSSQL.MIQ_Dev\MSSQL.2\MSSQL\Data\MIQDesign2Detach.mdf' cannot be overwritten. It is being used by database 'MIQDesignTest2'.
File 'MIQDesign' cannot be restored to 'D:\MSSQL.MIQ_Dev\MSSQL.2\MSSQL\Data\MIQDesign2Detach.mdf'. Use WITH MOVE to identify a valid location for the file.
The file 'D:\MSSQL.MIQ_Dev\MSSQL.2\MSSQL\Data\MIQDesign2Detach.ldf' cannot be overwritten. It is being used by database 'MIQDesignTest2'.
File 'MIQDesign_log' cannot be restored to 'D:\MSSQL.MIQ_Dev\MSSQL.2\MSSQL\Data\MIQDesign2Detach.ldf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE DATABASE is terminating abnormally.

为什么要覆盖原来的mdf？ RelocateFiles 不是应该指定您希望将其保存到不同 物理文件名吗？

Answer 1

这是有效的。

public class DatabaseManager
{
    public Action<int, string> OnSqlBackupPercentComplete;
    public Action<int, string> OnSqlRestorePercentComplete;
    public Action<SqlError> OnSqlBackupComplete;
    public Action<SqlError> OnSqlRestoreComplete;

    public bool IsConnected { get; private set; }

    private ServerConnection _connection;

    public void Connect(string userName, string password, string serverName, bool useInteratedLogin)
    {
        if (useInteratedLogin)
        {
            var sqlCon = new SqlConnection(string.Format("Data Source={0}; Integrated Security=True; Connection Timeout=5", serverName));
            _connection = new ServerConnection(sqlCon);
            _connection.Connect();
            IsConnected = true;
        }
        else
        {
            _connection = new ServerConnection(serverName, userName, password);
            _connection.ConnectTimeout = 5000;
            _connection.Connect();
            IsConnected = true;
        }

    }

    public void BackupDatabase(string databaseName, string destinationPath)
    {
        var sqlServer = new Server(_connection);


        databaseName = databaseName.Replace("[", "").Replace("]", "");
        var sqlBackup = new Backup
            {
                Action = BackupActionType.Database,
                BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString(),
                BackupSetName = "Archive",
                Database = databaseName
            };

        var deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);

        sqlBackup.Initialize = true;
        sqlBackup.Checksum = true;
        sqlBackup.ContinueAfterError = true;

        sqlBackup.Devices.Add(deviceItem);
        sqlBackup.Incremental = false;
        sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);

        sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;
        sqlBackup.PercentCompleteNotification = 10;
        sqlBackup.PercentComplete += (sender, e) => OnSqlBackupPercentComplete(e.Percent, e.Message);
        sqlBackup.Complete += (sender, e) => OnSqlBackupComplete(e.Error);
        sqlBackup.FormatMedia = false;
        sqlBackup.SqlBackup(sqlServer);


    }

    public DatabaseCollection GetDatabasesList()
    {
        if (IsConnected)
        {
            var sqlServer = new Server(_connection);
            return sqlServer.Databases;
        }
        return null;
    }



    public void RestoreDatabase(string databaseName, string filePath)
    {
        var sqlServer = new Server(_connection);

        databaseName = databaseName.Replace("[", "").Replace("]", "");

        var sqlRestore = new Restore();
        sqlRestore.PercentCompleteNotification = 10;
        sqlRestore.PercentComplete += (sender, e) => OnSqlRestorePercentComplete(e.Percent, e.Message);
        sqlRestore.Complete += (sender, e) => OnSqlRestoreComplete(e.Error);

        var deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
        sqlRestore.Devices.Add(deviceItem);
        sqlRestore.Database = databaseName;

        DataTable dtFileList = sqlRestore.ReadFileList(sqlServer);

        int lastIndexOf = dtFileList.Rows[1][1].ToString().LastIndexOf(@"\");
        string physicalName = dtFileList.Rows[1][1].ToString().Substring(0, lastIndexOf + 1);
        string dbLogicalName = dtFileList.Rows[0][0].ToString();
        string dbPhysicalName = physicalName + databaseName + ".mdf";
        string logLogicalName = dtFileList.Rows[1][0].ToString();
        string logPhysicalName = physicalName + databaseName + "_log.ldf";
        sqlRestore.RelocateFiles.Add(new RelocateFile(dbLogicalName, dbPhysicalName));
        sqlRestore.RelocateFiles.Add(new RelocateFile(logLogicalName, logPhysicalName));

        sqlServer.KillAllProcesses(sqlRestore.Database);

        Database db = sqlServer.Databases[databaseName];
        if (db != null)
        {
            db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
            db.Alter(TerminationClause.RollbackTransactionsImmediately);
            sqlServer.DetachDatabase(sqlRestore.Database, false);
        }

        sqlRestore.Action = RestoreActionType.Database;
        sqlRestore.ReplaceDatabase = true;

        sqlRestore.SqlRestore(sqlServer);
        db = sqlServer.Databases[databaseName];
        db.SetOnline();
        sqlServer.Refresh();
        db.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple;
    }

    public void Disconnect()
    {
        if (IsConnected)
            _connection.Disconnect();

        IsConnected = false;
    }
}