下文为您介绍使用SQL语句还原、分离及删除数据库连接的方法,如果您也在这方面遇到过问题,不妨一看,相信对您会有所帮助。
***步:删除连接
网上的方法:创建一个存储过程,然后调用,可以删除连接
USE [master]
GO
/****** Object: StoredProcedure [dbo].[p_killspid] Script Date: 07/29/2010 10:58:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[p_killspid]
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec( 'kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb #p#
我的方法类似:直接运行的,懒得创建存储过程,放在一个文件里,每次运行,差不多!(查询的位置有些区别,我的数据库sql server 2008)
use master
go
declare @dbName nvarchar(50)
set @dbName='Test' --数据库名
declare @spid nvarchar(20)
declare cur_lock cursor for
SELECT DISTINCT request_session_id FROM master.sys.dm_tran_locks WHERE resource_type = 'DATABASE' AND resource_database_id = db_id(@dbName)
open cur_lock
fetch cur_lock into @spid
while @@fetch_status=0
begin
exec( 'kill '+@spid)
fetch Next From cur_lock into @spid
end
close cur_lock
deallocate cur_lock#p#
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\P_backup_2010_07_28_180449_1980752.bak'
WITH FILE = 1,
MOVE N'P' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.mdf',
MOVE N'P_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf',
NOUNLOAD,
REPLACE,
STATS = 10
附:分离数据,也是要先删除连接才可以