跳至正文

Sql server用asp.net还原与恢复

上次做了个项目,涉及到数据库的还原和恢复,到网上找了一下,是利用SQLDMO实现的,只要添加SQLDMO引用就好了,然后利用下边的类的方法就可以实现了。


  我把原作者的类扩充了一下,可以自动识别web.config里 的数据库连接字符串,可以通过变量设置还原恢复的信息。





  需要注意的时还原,还原的时候问题最大了,有别的用户使用数据库的时候无法还原,解决办法就是在MASTER数据库中添加一个存储过程:




  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 getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin exec(’kill ‘+@spid)




  fetch next from getspid into @spid end close getspid deallocate getspid end GO




  在还原之前先执行这个存储过程,需要传递dbname,就是你的数据库的名字。下边是类的原代码:(web.config里的数据库连接字符串是constr)

 




using System; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace web.base_class { /// /// DbOper类,主要应用SQLDMO实现对Microsoft SQL Server数据库的备份和恢复 /// public class DbOper { private string server; private string uid; private string pwd; private string database; private string conn; /// /// DbOper类的构造函数 /// 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; } /// /// 数据库备份 /// 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 = 数据库备份; oBackup.Initialize = true; oBackup.SQLBackup(oSQLServer); return true; } catch { return falsethrow; } finally { oSQLServer.DisConnect(); } }

 






///  
 
/// 数据库恢复
 
///
 
public string DbRestore(string url)
 
{
 
if(exepro()!=true)//执行存储过程
 
{
 
return “操作失败”;
 
}
 
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 “恢复数据库失败”;
 
throw;
 
}
 
finally
 
{
 
oSQLServer.DisConnect();
 
}
 
}
 
}
/FooterTemplate> >
 
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();
 
}
 
}
 
}
 
}