2016年3月11日，湖北省荆门市审计局许方义同志在审计署网站“理论探讨”栏目发表的《浅谈审计大数据分析快速定位表及字段方法》，引起了笔者的共鸣。本人刚好也参加了今年年初署里统一安排的地方财政信息系统数据采集工作。当时县（市）、区级的财政报送的信息系统有一半都是扩展名为bak 的SQL数据库备份文件，地区合计有近800多个。

地区汇总接收人员，即要验证备份文件能否还原，又要对数据库及文件的命名规范进行检查，发现不符合规范的备份文件还要按通知要求更改。如果手工一个一个的还原检查，耗时耗力，难以完成。编写存储过程让计算机自行批量检查并完成数据库与备份同名的工作达到事半功倍的效果。

首先定义存储过程：

create PROC PROC_RESTORE_BACKUP

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

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

AS

DECLARE @DBNAME NVARCHAR(600)

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

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

CREATE TABLE #FILELISTINFO ---定义临时表存放RESTORE FILELISTONLY 读出的信息

(

LOGICALNAME NVARCHAR(128) NULL,

PHYSICALNAME NVARCHAR(260) NULL,

TYPE CHAR(1) NULL,

FILEGROUPNAME NVARCHAR(128) NULL,

FILESIZE BIGINT NULL ,

FILEMAXSIZE BIGINT NULL,

FILEID BIGINT,

CREATELSN NUMERIC(25,0),

DROPLSN NUMERIC(25,0) NULL,

UNIQUEID UNIQUEIDENTIFIER,

READONLYLSN NUMERIC(25,0) NULL,

READWRITELSN NUMERIC(25,0) NULL,

BACKUPSIZEINBYTES BIGINT,

SOURCEBLOCKSIZE INT,

FILEGROUPID INT,

LOGGROUPGUID UNIQUEIDENTIFIER NULL,

DIFFERENTIALBASELSN NUMERIC(25,0) NULL,

DIFFERENTIALBASEGUID UNIQUEIDENTIFIER,

ISREADONLY BIT,

ISPRESENT BIT,

TDETHUMBPRINT NVARCHAR(200)

)

DECLARE @FILELISTSQL VARCHAR(8000)

SET @FILELISTSQL = 'RESTORE FILELISTONLY FROM DISK=''' + @DIRECTORY + ''''

INSERT INTO #FILELISTINFO EXEC(@FILELISTSQL)

DECLARE @DLNAME NVARCHAR(128), @DPNAME NVARCHAR(260) --数据文件

DECLARE @LLNAME NVARCHAR(128), @LPNAME NVARCHAR(260) -- 逻辑文件

SELECT @DLNAME=LOGICALNAME,@DPNAME=PHYSICALNAME FROM #FILELISTINFO WHERE TYPE='D'

SELECT @LLNAME=LOGICALNAME,@LPNAME=PHYSICALNAME FROM #FILELISTINFO WHERE TYPE='L'

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

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

SET @TOLOGFILE = @PREDSTDIRECTORY + @DBNAME + '_LOG.LDF'

BEGIN TRY

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

RESTORE DATABASE @DBNAME

FROM DISK = @DIRECTORY

WITH MOVE @DLNAME TO @TODATAFILE,

MOVE @LLNAME TO @TOLOGFILE,

REPLACE

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

BACKUP DATABASE @DBNAME

TO DISK = @DIRECTORY

WITH FORMAT

END TRY

BEGIN CATCH

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

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

END CATCH

DROP TABLE #FILELISTINFO

GO

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

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

declare @strPath as varchar(50)

declare @a as varchar(200)

set @strPath='D:\bak文件' --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:\backup\' --存放还原后数据库的路径

FETCH NEXT FROM CRBAKLIST INTO @FILENAME

END

CLOSE CRBAKLIST

DEALLOCATE CRBAKLIST

执行结果为原sql数据库文件被自动完成还原、改名、重新备份覆盖原文件等操作，操作过程中某个文件发生错误，产生提示。

通过这个脚本可以达到对大量SQL数据库备份文件批量整理的目的。（庞子涛）