create procedure usp_restore_database @dbname varchar(250), -- 数据库名
@filepath varchar(250) -- 备份文件路径
as
declare @id varchar(20),@spid varchar(20)
/* 恢复数据库是不能使用数据库,kill掉所有连接后进行备份 */
select @id=db_id(@dbname) --dbid from sysdatabases where name=@dbname
declare cur_pro cursor for select spid from master.dbo.sysprocesses where dbid=@id
open cur_pro
fetch next from cur_pro into @spid
while @@fetch_status=0
begin
SELECT @@spid
if @@spid<>@spid -- 不kill掉自身连接
begin
exec ('kill ' + @spid) -- 似乎是这地方有问题??
end
fetch next from cur_pro into @spid
end
close cur_pro
deallocate cur_pro
restore database @dbname from disk=@filepath
@filepath varchar(250) -- 备份文件路径
as
declare @id varchar(20),@spid varchar(20)
/* 恢复数据库是不能使用数据库,kill掉所有连接后进行备份 */
select @id=db_id(@dbname) --dbid from sysdatabases where name=@dbname
declare cur_pro cursor for select spid from master.dbo.sysprocesses where dbid=@id
open cur_pro
fetch next from cur_pro into @spid
while @@fetch_status=0
begin
SELECT @@spid
if @@spid<>@spid -- 不kill掉自身连接
begin
exec ('kill ' + @spid) -- 似乎是这地方有问题??
end
fetch next from cur_pro into @spid
end
close cur_pro
deallocate cur_pro
restore database @dbname from disk=@filepath
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货