利用存储过程，轻松实现批量还原Bak

1.编写存储过程

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROC [dbo].[PROC_RESTORE_BACKUP]

@DIRECTORY NVARCHAR(1000), --bak文件所在路径变量

@PREDSTDIRECTORY NVARCHAR(600) --还原后数据库的存放路径变量

AS

DECLARE @DBNAME NVARCHAR(600)
declare @LogName Nvarchar(600)

SET @DBNAME=SUBSTRING(@DIRECTORY,LEN(@DIRECTORY)- CHARINDEX ('\',

REVERSE(@DIRECTORY )) + 2, CHARINDEX('\',REVERSE(@DIRECTORY ))- 5 )


DECLARE @TODATAFILE NVARCHAR(1000), @TOLOGFILE NVARCHAR(1000)

SET @TODATAFILE = @PREDSTDIRECTORY + @DBNAME+ '.MDF'

SET @TOLOGFILE = 'D:\\MSSQL\\LOG\\'  +@DBNAME+ '_LOG.LDF'
set @LogName = @DBNAME+'_Log'


BEGIN TRY

-- 还原数据库，生成MDF，LDF文件，与原BAK文件同名

RESTORE DATABASE @DBNAME

FROM DISK = @DIRECTORY

WITH MOVE @DBNAME TO @TODATAFILE,

MOVE @LogName TO @TOLOGFILE,

REPLACE

--备份现数据库，产生的备份文件将覆盖原BAK文件

--BACKUP DATABASE @DBNAME

--TO DISK = @DIRECTORY

--WITH FORMAT

END TRY

BEGIN CATCH

SELECT '-------------------------------------------------------------'

SELECT 'ERROR! @' + @DIRECTORY + '@ ' + ERROR_MESSAGE() --发生错误提示

END CATCH

2.利用存储过程进行还原

--接下来通过设定好的两个变量，利用游标遍历所设路径的文件，完成想要的工作。

USE master --打开事先建好的数据库

declare @strPath as varchar(50)

declare @a as varchar(200)

set @strPath='D:\09' --bak文件所在的目录

SET NOCOUNT ON --调用存储过程不返回计数

declare @FileList table(id int identity(1,1) primary key,fillname varchar(200),death int,isfile int)

insert into @FileList

EXEC Master.dbo.xp_DirTree @strPath,1,1

DECLARE @FILENAME NVARCHAR(600)

DECLARE CRBAKLIST CURSOR FOR

SELECT [fillname] FROM @FileList

OPEN CRBAKLIST

FETCH NEXT FROM CRBAKLIST INTO @FILENAME

WHILE @@FETCH_STATUS = 0

BEGIN

print @strPath+'\' +@FILENAME

set @a = @strPath+'\' +@FILENAME

EXEC PROC_RESTORE_BACKUP @a,'D:\\MSSQL\\DATA\\' --存放还原后数据库的路径

FETCH NEXT FROM CRBAKLIST INTO @FILENAME

END

CLOSE CRBAKLIST

DEALLOCATE CRBAKLIST

文章转载请说明出处：八零岁月 » 利用存储过程，轻松实现批量还原Bak