省厅信息中心：巧用存储过程实现SQL SERVER数据库批量导入

13.10.2016 15:06

本文来源: 审计厅

　　随着大数据时代的到来，对于计算机审计人员来说，采集和处理海量的电子数据变得越来越重要，如何高效、准确的采集和处理对计算机审计工作的顺利开展有着重要意义。2015年底，我们在对2012年至2015年全省财政收支电子数据进行采集、整理过程中，共采集了SQL SERVER数据库备份逾5000个，为了确保采集数据的可用性，需要将这些数据库备份逐一恢复到数据库中，完成校验。面对这样的海量数据，人工逐一还原显然不可行，于是我们开始尝试探索一个批量自动化导入的方法，下面将我们的实现方法介绍如下：

　　要实现数据库批量自动还原，首先要实现用程序代码还原数据库，再通过循环调用程序代码，从而实现批量还原功能。按照这个思路，将这项工作分解为以下两个步骤进行：

　　一、创建存储过程，实现备份数据库自动导入

　　首先在SQL SERVER数据库中进行存储过程的创建。通过数据库的RESTORE DATABASE命令，来实现数据库的还原，在还原的时候，逻辑文件名不可以修改，指定的物理文件存放的位置必须存在，通过使用restore filelistonly from disk＝ss.bak来查看数据库原来的逻辑文件名，然后再进行恢复。存储过程的代码分享如下：

　　create PROC [dbo].[PROC_RESTORE_BACKUP]

　　 @DIRECTORY NVARCHAR(1000),

　　 @PREDSTDIRECTORY NVARCHAR(600)

　　DECLARE @DBNAME NVARCHAR(600)

　　SET @DBNAME = SUBSTRING(@DIRECTORY, 1, CHARINDEX( '.',@DIRECTORY) - 1)

　　SET @DBNAME = SUBSTRING(@DIRECTORY, LEN(@DIRECTORY)-CHARINDEX('\',REVERSE(@DIRECTORY )) + 2, CHARINDEX('\',REVERSE(@DIRECTORY ))- 5 )

　　CREATE TABLE #FILELISTINFO

　　(

　　 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) --DATA FILE

　　DECLARE @LLNAME NVARCHAR(128) -- LOG FILE

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

　　SELECT @LLNAME=LOGICALNAME 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

　　END TRY

　　BEGIN CATCH

　　 SELECT 'ERROR! @' + @DIRECTORY + '@ ' + ERROR_MESSAGE()

　　END CATCH

　　DROP TABLE #FILELISTINFO

　　二、调用创建的存储过程，实现循环批量导入

　　上述创建的存储过程，调用时需要传递两个变量，一个是@DIRECTORY，代表数据库备份文件所在位置的绝对路径，另一个是 @PREDSTDIRECTORY，代表还原数据库后，生成的数据库文件的位置。对于@PREDSTDIRECTORY，可以指定一个固定路径即可，对于@DIRECTORY，需要查找到所有需要导入的数据库备份文件的路径，创建循环，逐一导入。根据数据库备份文件存放路径的不同情况，共有两种实现方法，分别介绍如下：

　　（一）使用批处理提取所有数据库文件路径（适用于数据库备份文件存放于多个不同文件夹的情况）

　　新建一个txt文档，在文档中输入如下代码：

　　保存完毕后，将该txt文件的后缀更改为.bat，双击运行该bat文件，则会在同一文件夹下，生成一个数据库文件路径列表.xls文件，该文件中即包括了F:/数据库备份/目录下所有的数据库文件的路径，如下图所示：　　

　　通过SQL SERVER的DTS功能，将该excel文件导入数据库的表[数据库文件路径列表]中。