如何通过C#代码恢复SQL Server数据库

时间:2021-07-05 16:27:56

I try to restore the database like this:

我尝试像这样恢复数据库:

SQL = @"RESTORE DATABASE MyDataBase TO DISK='d:\MyDATA.BAK'";
                Cmd = new SqlCommand(SQL, Conn);
                Cmd.ExecuteNonQuery();
                Cmd.Dispose();

but I always get error:

但我总是得到错误:

Msg 3102, Level 16, State 1, Line 7
RESTORE cannot process database 'MyDataBase ' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

消息3102,级别16,状态1,行7 RESTORE无法处理数据库'MyDataBase',因为此会话正在使用它。建议在执行此操作时使用master数据库。消息3013,级别16,状态1,行7 RESTORE DATABASE异常终止。

4 个解决方案

#1


14  

I prefer to use SMO to restore a backup:

我更喜欢使用SMO来恢复备份:

Microsoft.SqlServer.Management.Smo.Server smoServer = 
     new Server(new ServerConnection(server));

Database db = smoServer.Databases['MyDataBase'];
string dbPath = Path.Combine(db.PrimaryFilePath, 'MyDataBase.mdf');
string logPath = Path.Combine(db.PrimaryFilePath, 'MyDataBase_Log.ldf');
Restore restore = new Restore();
BackupDeviceItem deviceItem = 
    new BackupDeviceItem('d:\MyDATA.BAK', DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = backupDatabaseTo;
restore.FileNumber = restoreFileNumber;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.SqlRestore(smoServer);

db = smoServer.Databases['MyDataBase'];
db.SetOnline();
smoServer.Refresh();
db.Refresh();

You'll need references to Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, and Microsoft.SqlServer.Management.Sdk.Sfc

您将需要对Microsoft.SqlServer.Smo,Microsoft.SqlServer.SmoExtended和Microsoft.SqlServer.Management.Sdk.Sfc的引用。

#2


13  

Your DB connection is most likely to the database you're trying to restore. So there is a DB shared lock which prevents the restore of your db

您的数据库连接最有可能是您尝试还原的数据库。因此,有一个数据库共享锁可以阻止数据库的恢复

Try this

尝试这个

SQL = @"USE master BACKUP DATABASE MyDataBase TO DISK='d:\MyDATA.BAK'";

Or change the connection details to use master DB

或者更改连接详细信息以使用主数据库

#3


1  

You must connect to the database server via a different database.

您必须通过其他数据库连接到数据库服务器。

So your connection string should take you to say "Master" or another database on the server, then you can complete the task at hand.

因此,您的连接字符串应该让您在服务器上说“Master”或其他数据库,然后您就可以完成手头的任务。

#4


1  

Your connection string should have a master database as default catalog to connect to.

您的连接字符串应具有master数据库作为要连接的默认目录。

#1


14  

I prefer to use SMO to restore a backup:

我更喜欢使用SMO来恢复备份:

Microsoft.SqlServer.Management.Smo.Server smoServer = 
     new Server(new ServerConnection(server));

Database db = smoServer.Databases['MyDataBase'];
string dbPath = Path.Combine(db.PrimaryFilePath, 'MyDataBase.mdf');
string logPath = Path.Combine(db.PrimaryFilePath, 'MyDataBase_Log.ldf');
Restore restore = new Restore();
BackupDeviceItem deviceItem = 
    new BackupDeviceItem('d:\MyDATA.BAK', DeviceType.File);
restore.Devices.Add(deviceItem);
restore.Database = backupDatabaseTo;
restore.FileNumber = restoreFileNumber;
restore.Action = RestoreActionType.Database;
restore.ReplaceDatabase = true;
restore.SqlRestore(smoServer);

db = smoServer.Databases['MyDataBase'];
db.SetOnline();
smoServer.Refresh();
db.Refresh();

You'll need references to Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, and Microsoft.SqlServer.Management.Sdk.Sfc

您将需要对Microsoft.SqlServer.Smo,Microsoft.SqlServer.SmoExtended和Microsoft.SqlServer.Management.Sdk.Sfc的引用。

#2


13  

Your DB connection is most likely to the database you're trying to restore. So there is a DB shared lock which prevents the restore of your db

您的数据库连接最有可能是您尝试还原的数据库。因此,有一个数据库共享锁可以阻止数据库的恢复

Try this

尝试这个

SQL = @"USE master BACKUP DATABASE MyDataBase TO DISK='d:\MyDATA.BAK'";

Or change the connection details to use master DB

或者更改连接详细信息以使用主数据库

#3


1  

You must connect to the database server via a different database.

您必须通过其他数据库连接到数据库服务器。

So your connection string should take you to say "Master" or another database on the server, then you can complete the task at hand.

因此,您的连接字符串应该让您在服务器上说“Master”或其他数据库,然后您就可以完成手头的任务。

#4


1  

Your connection string should have a master database as default catalog to connect to.

您的连接字符串应具有master数据库作为要连接的默认目录。