从一个目录的备份文件中自动产生SQLServer恢复脚本
问题
数据库管理员眼下的一个挑战是备份和恢复数据库。备份在自动调度表中完成，但是恢复可以采取很多不同的版本，你可能需要恢复一个产品数据库，恢复一个发展包，或者测试数据库，或者只在另外的地方创建数据库副本。有一些自动执行恢复过程和创建脚本的方法，但是这个方法显示了一种方式，那就是只读取存在于备份文件中的目录的内容。
专家解答
以下是读取目录中的内容和创建需要通过执行来恢复数据库的恢复命令的一种简单方法。这个脚本可以用于全部备份，差异备份和事务日志备份。
在我们开始之前，下面的脚本假设情况如下：
1、 恢复的数据库拥有和备份数据库一样的名称。
2、 恢复的数据库和备份数据库存储在相同的位置。
3、 文件名具有以下的格式
dbName_YYYYMMDDHHMM.xxx
4、 文件扩展名如下
全部备份 – BAK
差异备份 – DIF
事务日志备份 – TRN
5、 XP_CMDSHELL 是可用的
6、 不存在可能破坏恢复链的缺失的事务日志
所以，让我们按照下面的步骤来创建自己的备份：
在午夜进行全部备份
早上3：15开始每三个小时进行一次差异备份
早上1点开始每半个小时进行一次日志备份
在上午九点，我们可能会得到以下创建于2008年9月10日的备份文件，这个备份文件遵从以上规则，命名为Customer。
Customer_200809100000.BAK
Customer_200809100100.TRN
Customer_200809100130.TRN
Customer_200809100200.TRN
Customer_200809100230.TRN
Customer_200809100300.TRN
Customer_200809100315.DIF
Customer_200809100330.TRN
Customer_200809100400.TRN
Customer_200809100430.TRN
Customer_200809100500.TRN
Customer_200809100530.TRN
Customer_200809100600.TRN
Customer_200809100615.DIF
Customer_200809100630.TRN
Customer_200809100700.TRN
Customer_200809100730.TRN
Customer_200809100800.TRN
Customer_200809100830.TRN
Customer_200809100900.TRN
如果我们想在早上九点对最新的全部备份，差异备份和事务日志备份做一个恢复，那么我们需要恢复以下的文件：
Customer_200809100000.BAK
Customer_200809100615.DIF
Customer_200809100630.TRN
Customer_200809100700.TRN
Customer_200809100730.TRN
Customer_200809100800.TRN
Customer_200809100830.TRN
Customer_200809100900.TRN
下面的脚本会读取目录并且为我们创建恢复脚本。仅有的两个需要改变的参数是@dbName 和 @backupPath。
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaratioN
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
如果你在一个查询窗口中运行以上代码，并且假设以上列出的文件是存在的，那么你将会得到下面的输出结果。在这一时刻，你可以把这些代码复制和粘贴到另一个查询窗口并且执行查询来做真正的恢复。
正如你所看到的，它做的是全部备份，最新的差异备份接着是所有的事务日志。这个脚本最后也使用了WITH RECOVERY来使数据库保持可用的状态。