利用SQLDMO实现的,只要添加SQLDMO引用就好了,然后利用下边的类的方法就可以实现了。
我把原作者的类扩充了一下,可以自动识别web.config里的数据库连接字符串,可以通过变量设置还原恢复的信息。
需要注意的时还原,还原的时候问题最大了,有别的用户使用数据库的时候无法还原,解决办法就是在MASTER数据库中添加一个存储过程:
createprockillspid(@dbnamevarchar(20))
as
begin
declare@sqlnvarchar(500)
declare@spidint
set@sql='declaregetspidcursorfor
selectspidfromsysprocesseswheredbid=db_id('''+@dbname+''')'
exec(@sql)
opengetspid
fetchnextfromgetspidinto@spid
while@@fetch_status<>-1
begin
exec('kill'+@spid)
fetchnextfromgetspidinto@spid
end
closegetspid
deallocategetspid
end
GO
在还原之前先执行这个存储过程,需要传递dbname,就是你的数据库的名字。下边是类的原代码:(web.config里的数据库连接字符串是constr)
usingSystem;
usingSystem.Configuration;
usingSystem.Data.SqlClient;
usingSystem.Data;
namespaceweb.base_class
{
///<summary>
///DbOper类,主要应用SQLDMO实现对MicrosoftSQLServer数据库的备份和恢复
///</summary>
publicclassDbOper
{
privatestringserver;
privatestringuid;
privatestringpwd;
privatestringdatabase;
privatestringconn;
///<summary>
///DbOper类的构造函数
///</summary>
publicDbOper()
{
conn=System.Configuration.ConfigurationSettings.AppSettings["constr"].ToString();
server=cut(conn,"server=",";");
uid=cut(conn,"uid=",";");
pwd=cut(conn,"pwd=",";");
database=cut(conn,"database=",";");
}
publicstringcut(stringstr,stringbg,stringed)
{
stringsub;
sub=str.Substring(str.IndexOf(bg)+bg.Length);
sub=sub.Substring(0,sub.IndexOf(";"));
returnsub;
}
///<summary>
///数据库备份
///</summary>
publicboolDbBackup(stringurl)
{
SQLDMO.BackupoBackup=newSQLDMO.BackupClass();
SQLDMO.SQLServeroSQLServer=newSQLDMO.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="数据库备份";
oBackup.Initialize=true;
oBackup.SQLBackup(oSQLServer);
returntrue;
}
catch
{
returnfalse;
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
///<summary>
///数据库恢复
///</summary>
publicstringDbRestore(stringurl)
{
if(exepro()!=true)//执行存储过程
{
return"操作失败";
}
else
{
SQLDMO.RestoreoRestore=newSQLDMO.RestoreClass();
SQLDMO.SQLServeroSQLServer=newSQLDMO.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(Exceptione)
{
return"恢复数据库失败";
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
}
privateboolexepro()
{
SqlConnectionconn1=newSqlConnection("server="+server+";uid="+uid+";pwd="+pwd+";database=master");
SqlCommandcmd=newSqlCommand("killspid",conn1);
mandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@dbname","port");
try
{
conn1.Open();
cmd.ExecuteNonQuery();
returntrue;
}
catch(Exceptionex)
{
returnfalse;
}
finally
{
conn1.Close();
}
}
}
}
本文转自高海东博客园博客,原文链接:/ghd258/archive//02/28/339458.html,如需转载请自行联系原作者