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 (N‘P‘, N‘PC‘))
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