Last time I worked on a project that involved database restoration and recovery. I searched online and found that it was implemented using SQLDMO. You just need to add a SQLDMO reference, and then use the methods of the following classes to achieve it.
I expanded the original author's class so that it can automatically recognize the database connection string in web.config and restore the recovered information through variable settings.
You need to pay attention when restoring. The biggest problem is when restoring. When other users use the database, they cannot restore. The solution is to add a stored procedure in the MASTER database:
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id( '''+@dbname+''')'
exec(@sql)
open getpid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
exec('kill '+@spid )
fetch next from getspid into @spid
end
close getpid
deallocategetpid
end
GO
To execute this stored procedure before restoring, you need to pass dbname, which is the name of your database. The following is the original code of the class: (the database connection string in web.config is constr)
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace web.base_class
{
/// <summary>
/// DbOper class, mainly uses SQLDMO to realize backup and recovery of Microsoft SQL Server database
/// </summary>
public class DbOper
{
private string server;
private string uid;
private string pwd;
private string database;
private string conn;
/// <summary>
/// Constructor of DbOper class
/// </summary>
public DbOper()
{
conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
server=cut(conn,"server=",";");
uid=cut(conn,"uid=",";");
pwd=cut(conn,"pwd=",";");
database=cut(conn,"database=",";");
}
public string cut(string str,string bg,string ed)
{
string sub;
sub=str.Substring(str.IndexOf(bg)+bg.Length);
sub=sub.Substring(0,sub.IndexOf(";"));
return sub;
}
/// <summary>
/// Database backup
/// </summary>
public bool DbBackup(string url)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server,uid, pwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = database;
oBackup.Files = url;//"d:Northwind.bak";
oBackup.BackupSetName = database;
oBackup.BackupSetDescription = "Database Backup";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
return true;
}
catch
{
return false;
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
/// <summary>
/// Database recovery
/// </summary>
public string DbRestore(string url)
{
if(exepro()!=true)//Execute the stored procedure
{
return "Operation failed";
}
else
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(server, uid, pwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = database;
oRestore.Files = url;//@"d:Northwind.bak";
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
return "ok";
}
catch(Exception e)
{
return "Failed to restore database";
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
}
private bool exepro()
{
SqlConnection conn1 = new SqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
SqlCommand cmd = new SqlCommand("killspid",conn1);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dbname","port");
try
{
conn1.Open();
cmd.ExecuteNonQuery();
return true;
}
catch(Exception ex)
{
return false;
}
finally
{
conn1.Close();
}
}
}
}