Thought of Genius

當資料庫發生異常的時候，通常都希望能在很短的時間內還原資料庫，每當問題發生時，再撰寫資料庫還原的T-SQL，往往耽誤了許多時間。所以我們可以事先撰寫還原的T-SQL，以備不時之需。
假設我們的備份策略為：
每天凌晨 AM 3:00 處理一次完整備份，每3個小時處理一次差異備份，每1個小時處理一次交易備份。

還原策略說明：
1. 備份結尾交易紀錄，並設定資料庫為還原模式。
2. 建立暫存資料表。
3. 檢查備份檔案相關資訊，並寫入暫存資料表。
4. 還原完整備份。
5. 還原最後一個差異備份。(如果沒有差異備份，則此步驟會跳過。)
6. 依順序還原最後一個差異備份之後所有的交易備份。(如果沒有差異備份，依順序還原完整備份之後所有交易備份。)
7. 還原結尾交易備份至特定時間點。

<br />USE master<br /><br />-- Variable announcement<br />DECLARE @DBName nvarchar(32)<br />DECLARE @DirectoryPath nvarchar(128)<br />DECLARE @FileName nvarchar(32)<br />DECLARE @FilePath nvarchar(128)<br />DECLARE @TailPath nvarchar(128)<br />DECLARE @StopTime datetime<br /><br />-- SET @DBName, @DirectoryPath, @FileName, @StopTime<br />SET @DBName = 'Lab'<br />SET @DirectoryPath = 'C:'<br />SET @FileName = 'Lab.bak'<br />SET @StopTime = '2008-11-03 10:38:19'<br />SET @FilePath = @DirectoryPath + '\' + @FileName<br />SET @TailPath = @DirectoryPath + '\' + @DBName + '_TailLog_' + CONVERT(varchar, GETDATE(), 112 ) +'_'+ REPLACE (CONVERT(varchar, GETDATE(), 108 ),':','') + '.bak'<br /><br />-- Backup TailLog <br />BACKUP LOG @DBName<br />TO DISK = @TailPath<br />WITH NAME = @DBName, DESCRIPTION = 'TailLog', NOFORMAT, INIT, NORECOVERY<br /><br />-- Create Temporary Table<br />-- SQL Server 2000, 2005, 2008 column quantity is different<br />CREATE TABLE #T (<br />BackupName nvarchar(128),<br />BackupDescription nvarchar(255),<br />BackupType smallint,<br />ExpirationDate datetime NULL,<br />Compressed tinyint,<br />Position smallint,<br />DeviceType tinyint NULL,<br />UserName nvarchar(128),<br />ServerName nvarchar(128) NULL,<br />DatabaseName nvarchar(128) NULL,<br />DatabaseVersion int NULL,<br />DatabaseCreationDate datetime NULL,<br />BackupSize numeric(20,0) NULL,<br />FirstLSN numeric(25,0) NULL,<br />LastLSN numeric(25,0) NULL,<br />CheckpointLSN numeric(25,0) NULL,<br />DatabaseBackupLSN numeric(25,0) NULL,<br />BackupStartDate datetime,<br />BackupFinishDate datetime,<br />SortOrder smallint NULL,<br />CodePage smallint NULL,<br />UnicodeLocaleId int NULL,<br />UnicodeComparisonStyle int NULL,<br />CompatibilityLevel tinyint NULL,<br />SoftwareVendorId int,<br />SoftwareVersionMajor int,<br />SoftwareVersionMinor int,<br />SoftwareVersionBuild int NULL,<br />MachineName nvarchar(128),<br />Flags int NULL,<br />BindingID uniqueidentifier NULL,<br />RecoveryForkID uniqueidentifier NULL,<br />Collation nvarchar(128) NULL,<br />FamilyGUID uniqueidentifier NULL,<br />HasBulkLoggedData bit NULL,<br />IsSnapshot bit NULL,<br />IsReadOnly bit NULL,<br />IsSingleUser bit NULL,<br />HasBackupChecksums bit NULL,<br />IsDamaged bit NULL,<br />BeginsLogChain bit NULL,<br />HasIncompleteMetaData bit NULL,<br />IsForceOffline bit NULL,<br />IsCopyOnly bit NULL,<br />FirstRecoveryForkID uniqueidentifier NULL,<br />ForkPointLSN numeric(25,0) NULL,<br />RecoveryModel nvarchar(60) NULL,<br />DifferentialBaseLSN numeric(25,0) NULL,<br />DifferentialBaseGUID uniqueidentifier NULL,<br />BackupTypeDescription nvarchar(60),<br />BackupSetGUID uniqueidentifier NULL,<br />CompressedBackupSize int NULL <br />)<br /><br />-- Check Backup SET and Insert INTO Temporary Table<br />INSERT #T EXEC('RESTORE HEADERONLY FROM DISK = '''+ @FilePath +'''')<br /><br />-- Restore Full Backup<br />RESTORE DATABASE @DBName<br />FROM DISK = @FilePath<br />WITH NORECOVERY,FILE = 1<br /><br />-- Restore Last Differential Backup<br />DECLARE @PtnD smallint<br />DECLARE @CheckD bit<br />SET @CheckD = 0<br />DECLARE CurD CURSOR FOR<br /> SELECT TOP(1) Position FROM #T WHERE BackupType = 5 ORDER BY BackupStartDate DESC, Position DESC<br />OPEN CurD<br />FETCH NEXT FROM CurD INTO @PtnD<br />WHILE(@@FETCH_STATUS=0)<br />BEGIN<br /> RESTORE DATABASE @DBName<br /> FROM DISK = @FilePath<br /> WITH NORECOVERY,FILE = @PtnD<br /> SET @CheckD = 1<br /> FETCH NEXT FROM CurD INTO @PtnD<br />END<br />CLOSE CurD<br />DEALLOCATE CurD<br /><br />-- Restore Log Backup From Last Differential Backup or From Full Backup<br />DECLARE @PtnL smallint<br />IF @CheckD = 1<br />BEGIN<br /> DECLARE CurL CURSOR FOR<br /> SELECT Position FROM #T WHERE Position > (SELECT TOP(1) Position FROM #T WHERE BackupType = 5 ORDER BY BackupStartDate DESC, Position DESC)<br /> OPEN CurL<br /> FETCH NEXT FROM CurL INTO @PtnL<br /> WHILE(@@FETCH_STATUS=0)<br /> BEGIN<br /> RESTORE LOG @DBName<br /> FROM DISK = @FilePath<br /> WITH NORECOVERY,FILE = @PtnL<br /> FETCH NEXT FROM CurL INTO @PtnL<br /> END<br /> CLOSE CurL<br /> DEALLOCATE CurL <br />END<br />ELSE<br />BEGIN<br /> DECLARE CurL CURSOR FOR<br /> SELECT Position FROM #T WHERE Position > 1<br /> OPEN CurL<br /> FETCH NEXT FROM CurL INTO @PtnL<br /> WHILE(@@FETCH_STATUS=0)<br /> BEGIN<br /> RESTORE LOG @DBName<br /> FROM DISK = @FilePath<br /> WITH NORECOVERY,FILE = @PtnL<br /> FETCH NEXT FROM CurL INTO @PtnL<br /> End<br /> CLOSE CurL<br /> DEALLOCATE CurL <br />End<br /><br />-- Restore TailLog Backup Stop at Specific Time<br />RESTORE LOG @DBName<br />FROM DISK = @TailPath<br />WITH RECOVERY, STOPAT = @StopTime, FILE = 1<br /><br />GO<br /><br /><br />