从一个目录的备份文件中自动产生SQL Server恢复脚本

核心提示： Customer_200809100400.TRNCustomer_200809100430.TRNCustomer_200809100500.TRNCustomer_200809100530.TRNCustomer_200809100600.TRNCustomer_20080910061

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来使数据库保持可用的状态。