sql 数据库还原脚本 (kill链接+独占

  在开发过程中经常会碰到数据库还原,要是sql 连接没完全释放掉,那么还原就会受到阻碍。次脚本就是为了解决这个问题。




USE
[master] GO /****** Object: StoredProcedure [dbo].[sp_backrecovery] Script Date: 03/12/2014 09:42:38 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N[dbo].[sp_backrecovery]) AND type in (NP, NPC)) DROP PROCEDURE [dbo].[sp_backrecovery] GO USE [master] GO /****** Object: StoredProcedure [dbo].[sp_backrecovery] Script Date: 03/12/2014 09:35:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --执行自动还原 /* exec [master].[dbo].sp_backrecovery ‘D:\SQLSERVER\back\uiaa‘,‘uiaa_test‘ */ create procedure [dbo].[sp_backrecovery] ( @Path nvarchar(260), @dbname varchar(200) ) as begin if RIGHT(@Path, 1) <> \ set @Path = @Path + \ declare @back table( id int identity, directory nvarchar(260), depth int, IsFile bit ) insert @back exec master.dbo.xp_dirtree @path = @path, @depth = 0, @file = 1 DECLARE @depth int, @depthMax int UPDATE @back SET directory = @Path + directory WHERE depth = 1 SELECT @depth = 2, @depthMax = MAX(depth) FROM @back WHILE @depth <= @depthMax BEGIN UPDATE A SET directory = ( SELECT TOP 1 directory FROM @back WHERE depth = @depth - 1 AND IsFile = 0 AND id < A.id ORDER BY id DESC ) + N/ + directory FROM @back A WHERE depth = @depth SET @depth= @depth + 1 END --kill 连接 declare @kills table ( id int identity(1,1), spid varchar(10) ) insert into @kills select spid from sysprocesses where dbid=db_id(@dbname) declare @i int declare @icount int set @i=1 select @icount=count(1) from @kills while @i<=@icount begin declare @spid varchar(10) select @spid=spid from @kills where id=@i declare @killsql varchar(200) set @killsql=kill + @spid exec (@killsql) set @i=@i+1 end --完整备份恢复 declare @filepath varchar(1000) select top 1 @filepath=directory from @back order by directory desc declare @sql varchar(1000) set @sql=alter database +@dbname+ set single_user exec (@sql) set @sql= restore database +@dbname+ from disk =‘‘‘+@filepath+‘‘‘ with replace,recovery exec (@sql) set @sql= alter database +@dbname+ set multi_user exec (@sql) end

sql 数据库还原脚本 (kill链接+独占,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。