1。首先把adoconnection连接连到master数据库 2。再打开sql服务管理器,在master数据库中建立如下存储过程, use master go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500),@temp varchar(1000) 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 =0 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end 3。用adocommand调用存储过程,将其它打开该数据库的进程kill掉 use master exec killspid '数据库名'如下: try DataM.BackADOCommand.CommandText:='Use Master'; DataM.BackADOCommand.Execute; DataM.BackADOCommand.CommandText:='exec killspid Gl'; //调用存储过程,将打开Gl数据库的进程kill掉 DataM.BackADOCommand.Execute; DataM.BackADOCommand.CommandText:='restore database Gl from disk=''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\gl.back'' with recovery,Replace'; DataM.BackADOCommand.Execute; DataM.BackADOCommand.CommandText:='Use Gl'; DataM.BackADOCommand.Execute; label1.Caption:='恢复成功!'; except ShowMessage('数据库正在被使用!请确定已关闭其它管理程序!'); Label1.Caption:='恢复失败!'; end;
祝你好运.
2。再打开sql服务管理器,在master数据库中建立如下存储过程,
use master
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
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 =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end 3。用adocommand调用存储过程,将其它打开该数据库的进程kill掉
use master
exec killspid '数据库名'如下:
try
DataM.BackADOCommand.CommandText:='Use Master';
DataM.BackADOCommand.Execute;
DataM.BackADOCommand.CommandText:='exec killspid Gl'; //调用存储过程,将打开Gl数据库的进程kill掉
DataM.BackADOCommand.Execute;
DataM.BackADOCommand.CommandText:='restore database Gl from disk=''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\gl.back'' with recovery,Replace';
DataM.BackADOCommand.Execute;
DataM.BackADOCommand.CommandText:='Use Gl';
DataM.BackADOCommand.Execute;
label1.Caption:='恢复成功!';
except
ShowMessage('数据库正在被使用!请确定已关闭其它管理程序!');
Label1.Caption:='恢复失败!';
end;