贴上一个存储过程: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
建议直接连接到Master来进行操作
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
private void button1_Click(object sender, System.EventArgs e)
{
string sql=null;
if( conn.State == ConnectionState.Open)
conn.Close();
conn.ConnectionString = ConnectionString;
conn.Open();
string nowpath=Application.StartupPath;
sql="backup database mydb to disk='"+nowpath+"\\数据备份文件(不要删除).dll"+"' with format,name='Full Backup of TradeDb/SQL'";
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("成功备份数据!备份记录为:"+nowpath+"\\数据备份文件(不要删除).dll"," 成功了!", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
catch(Exception err)
{
MessageBox.Show("出现错误,请与作者联系!"+err.ToString() ,"出错了!",MessageBoxButtons.OK ,MessageBoxIcon.Question );
}
finally
{
conn.Close();
this.Close();
}
恢复时:
private void button1_Click(object sender, System.EventArgs e)
{
string sql=null;
if( conn.State == ConnectionState.Open)
conn.Close();
ConnectionString = "data source="+Form1.HostName+";initial catalog=master;persist security info=False;user id=sa;pwd="+Form1.DataPass+";";
conn.ConnectionString=ConnectionString;
conn.Open();
try
{
string nowpath=Application.StartupPath;
//进入master
sql="use master";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
try
{
//执行存储过程杀掉其它进程
sql="exec killspid 'mydb'";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
finally
{
FileInfo backupdata = new FileInfo(nowpath+"\\数据备份文件(不要删除).dll");
if (backupdata.Exists)
{ sql="restore database mydb from disk='"+nowpath+"\\数据备份文件(不要删除).dll"+"' with recovery";
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("成功恢复数据!从"+nowpath+"\\数据备份文件(不要删除).dll 中恢复"," 成功了!", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
else
{
MessageBox.Show("你可能以前没有备份过数据库!数据备份文件(不要删除).dll 不存在!"," 错误!", MessageBoxButtons.OK, MessageBoxIcon.Information );
}
}
必须在还原前将所有用户连接断开
通常是先把数据库状态设置为dbo use only
exec sp_dboption 'northwind','dbo use only','true'
然后才可以进行恢复,没有好的办法
(最痛快的方法是重新启动服务器)
也可以使用如下语句先把数据库设置成单用户模式
alter database 数据库名 SET SINGLE_USER还原数据库再把数据库设置成多用户模式alter database 数据库名 SET MULTI_USER