我们这里通过SQLDMO实现数据库的备份与恢复操作,首先添加SQLDMO的引用
实现数据库备份与恢复的操作类:
using
System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/**/ /// <summary>
/// dbDataBack 的摘要说明
/// 实现数据库的备份与恢复
/// </summary>
public class dbDataBack
... {
/**//// <summary>
/// 服务器
/// </summary>
private string server;
/**//// <summary>
/// 登录名
/// </summary>
private string uid;
/**//// <summary>
/// 登录密码
/// </summary>
private string pwd;
/**//// <summary>
/// 要操作的数据库
/// </summary>
private string database;
/**//// <summary>
/// 数据库链接字符串
/// </summary>
private string conn;
/**//// <summary>
/// dbDataBack类的构造函数
/// 在这里进行字符串的切割,获取服务器,登录名,密码,数据库
/// </summary>
public dbDataBack()
...{
conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
server = StringCut(conn, "data Source=",";");
uid = StringCut(conn, "user id=",";");
pwd = StringCut(conn, "pwd=", ";");
database = StringCut(conn, "database=", ";");
}
/**//// <summary>
/// 字符串切割函数
/// </summary>
/// <param name="str"></param>
/// <param name="bg"></param>
/// <param name="ed"></param>
/// <returns></returns>
public string StringCut(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>
/// 构造文件名
/// </summary>
/// <returns></returns>
private string createpath()
...{
string CurrTime = System.DateTime.Now.ToString();
CurrTime = CurrTime.Replace("-", "");
CurrTime = CurrTime.Replace(":", "");
CurrTime = CurrTime.Replace(" ", "");
CurrTime = CurrTime.Substring(0, 12);
string path = @"F:/Norkeweb2.0/databack/";
path += database;
path += "_db_";
path += CurrTime;
path += ".bak";
return path;
}
/**//// <summary>
/// 数据库备份
/// </summary>
/// <returns></returns>
public bool dbbackup()
...{
string path = createpath();
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 = path;
oBackUp.BackupSetName = database;
oBackUp.BackupSetDescription = "数据库备份";
oBackUp.Initialize = true;
oBackUp.SQLBackup(oSQLServer);
return true;
}
catch (Exception ex)
...{
return false;
throw ex;
}
finally
...{
oSQLServer.DisConnect();
}
}
/**//// <summary>
/// 数据库恢复
/// </summary>
/// <returns></returns>
public string dbrestore()
...{
if(exepro()!=true)
...{
return "操作失败!";
}
else
...{
SQLDMO.Restore oRestore=new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer=new SQLDMO.SQLServerClass();
try
...{
exepro();
oSQLServer.LoginSecure=false;
oSQLServer.Connect(server,uid,pwd);
oRestore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database=database;
oRestore.Files=@"F:/Norkeweb2.0/databack/web.bak"; //数据库备份文件
oRestore.FileNumber=1;
oRestore.ReplaceDatabase=true;
oRestore.SQLRestore(oSQLServer);
return "ok";
}
catch(Exception ex)
...{
return "恢复数据库失败!";
throw ex;
}
finally
...{
oSQLServer.DisConnect();
}
}
}
/**//// <summary>
/// 杀死当前库的所有进程
/// </summary>
/// <returns></returns>
private bool exepro()
...{
SqlConnection conn1=new SqlConnection("server="+server+";user id="+uid+";pwd="+pwd+";database=master");
SqlCommand cmd=new SqlCommand("killspid",conn1);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@dbname","web");
try
...{
conn1.Open();
cmd.ExecuteNonQuery();
return true;
}
catch(Exception ex)
...{
return false;
}
finally
...{
conn1.Close();
}
}
}
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/**/ /// <summary>
/// dbDataBack 的摘要说明
/// 实现数据库的备份与恢复
/// </summary>
public class dbDataBack
... {
/**//// <summary>
/// 服务器
/// </summary>
private string server;
/**//// <summary>
/// 登录名
/// </summary>
private string uid;
/**//// <summary>
/// 登录密码
/// </summary>
private string pwd;
/**//// <summary>
/// 要操作的数据库
/// </summary>
private string database;
/**//// <summary>
/// 数据库链接字符串
/// </summary>
private string conn;
/**//// <summary>
/// dbDataBack类的构造函数
/// 在这里进行字符串的切割,获取服务器,登录名,密码,数据库
/// </summary>
public dbDataBack()
...{
conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
server = StringCut(conn, "data Source=",";");
uid = StringCut(conn, "user id=",";");
pwd = StringCut(conn, "pwd=", ";");
database = StringCut(conn, "database=", ";");
}
/**//// <summary>
/// 字符串切割函数
/// </summary>
/// <param name="str"></param>
/// <param name="bg"></param>
/// <param name="ed"></param>
/// <returns></returns>
public string StringCut(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>
/// 构造文件名
/// </summary>
/// <returns></returns>
private string createpath()
...{
string CurrTime = System.DateTime.Now.ToString();
CurrTime = CurrTime.Replace("-", "");
CurrTime = CurrTime.Replace(":", "");
CurrTime = CurrTime.Replace(" ", "");
CurrTime = CurrTime.Substring(0, 12);
string path = @"F:/Norkeweb2.0/databack/";
path += database;
path += "_db_";
path += CurrTime;
path += ".bak";
return path;
}
/**//// <summary>
/// 数据库备份
/// </summary>
/// <returns></returns>
public bool dbbackup()
...{
string path = createpath();
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 = path;
oBackUp.BackupSetName = database;
oBackUp.BackupSetDescription = "数据库备份";
oBackUp.Initialize = true;
oBackUp.SQLBackup(oSQLServer);
return true;
}
catch (Exception ex)
...{
return false;
throw ex;
}
finally
...{
oSQLServer.DisConnect();
}
}
/**//// <summary>
/// 数据库恢复
/// </summary>
/// <returns></returns>
public string dbrestore()
...{
if(exepro()!=true)
...{
return "操作失败!";
}
else
...{
SQLDMO.Restore oRestore=new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer=new SQLDMO.SQLServerClass();
try
...{
exepro();
oSQLServer.LoginSecure=false;
oSQLServer.Connect(server,uid,pwd);
oRestore.Action=SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database=database;
oRestore.Files=@"F:/Norkeweb2.0/databack/web.bak"; //数据库备份文件
oRestore.FileNumber=1;
oRestore.ReplaceDatabase=true;
oRestore.SQLRestore(oSQLServer);
return "ok";
}
catch(Exception ex)
...{
return "恢复数据库失败!";
throw ex;
}
finally
...{
oSQLServer.DisConnect();
}
}
}
/**//// <summary>
/// 杀死当前库的所有进程
/// </summary>
/// <returns></returns>
private bool exepro()
...{
SqlConnection conn1=new SqlConnection("server="+server+";user id="+uid+";pwd="+pwd+";database=master");
SqlCommand cmd=new SqlCommand("killspid",conn1);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@dbname","web");
try
...{
conn1.Open();
cmd.ExecuteNonQuery();
return true;
}
catch(Exception ex)
...{
return false;
}
finally
...{
conn1.Close();
}
}
}
对应的存储过程:
1.sql 2005下面的写法:
/**/
/*杀死指定数据库的所有进程的存储过程,该存储过程只能放到数据库master下面*/
ALTER PROCEDURE killspid
(
@dbname varchar( 20 )
)
AS
declare @sql nvarchar( 500 )
declare @spid int
set @sql = ' declare getspid cursor for select spid from sysprocesses where dbid=db_id( ''' + @dbname + ''' ) '
execute(@sql)
open getspid fetch next from getspid into @spid
while @@fetch_status <>- 1
begin
execute( ' kill ' + @spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
return
ALTER PROCEDURE killspid
(
@dbname varchar( 20 )
)
AS
declare @sql nvarchar( 500 )
declare @spid int
set @sql = ' declare getspid cursor for select spid from sysprocesses where dbid=db_id( ''' + @dbname + ''' ) '
execute(@sql)
open getspid fetch next from getspid into @spid
while @@fetch_status <>- 1
begin
execute( ' kill ' + @spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
return
2.sql2000下面的写法:
CREATE PROCEDURE 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 + ''' ) '
EXECUTE (@sql)
OPEN getspid FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status <>- 1
BEGIN
EXECUTE( ' kill ' + @spid) FETCH NEXT FROM getspid INTO @spid
END
CLOSE getspid
DEALLOCATE getspid
END
GO
(
@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 + ''' ) '
EXECUTE (@sql)
OPEN getspid FETCH NEXT FROM getspid INTO @spid
WHILE @@fetch_status <>- 1
BEGIN
EXECUTE( ' kill ' + @spid) FETCH NEXT FROM getspid INTO @spid
END
CLOSE getspid
DEALLOCATE getspid
END
GO