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 />

復原模式(Recovery Model) :
發生檢查點(check point)之後，是否繼續存留交易紀錄在資料庫的交易紀錄檔之中。

完整備份(Full Backup) : Data + Uncommitted Log
將整個資落庫的資料與部分交易。在作業的過程中任何的作業完成與未完成的交易都會進行備份。

差異備份(Differential Backup) :
1.從最近一次的完整備份，到現在的資料量變化的備份作業。
2.僅能將資料庫還原至差異備份的時間點。
3.最近一次的差異備份可以取代前一次的差異備份。

交易紀錄備份(Log Backup) : Backup Log + Clear Log
1.進行交易備份之後，系統會自動截斷不在使用的交易紀錄檔，並釋放空間。
2.每一份交易備份的備份資料，是屬於接續性的關係。
3.可以將系統狀態還原至特定的時間點。

結尾交易備份(Tail-log Backup) :
當系統要進行還原時，第一個首要工作就是，將剩餘的交易紀錄檔進行備份。

<br />-- Create database<br />USE master<br />GO<br />IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Lab') DROP DATABASE Lab <br />GO<br />CREATE DATABASE [Lab] ON PRIMARY <br />( NAME = N'Lab_DATA', FILENAME = N'C:\Lab_DATA.MDF' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG01] <br />( NAME = N'Lab_FG01', FILENAME = N'C:\Lab_FG01.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG02] <br />( NAME = N'Lab_FG02', FILENAME = N'C:\Lab_FG02.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), <br /> FILEGROUP [Lab_FG03] <br />( NAME = N'Lab_FG03', FILENAME = N'C:\Lab_FG03.NDF' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)<br /> LOG ON <br />( NAME = N'Lab_LOG', FILENAME = N'C:\Lab_LOG.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)<br />GO<br /><br />-- Full Backup<br />USE master<br />GO<br />BACKUP DATABASE Lab <br />TO DISK='C:\Lab.bak'<br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Full_Backup', <br />NOFORMAT, INIT <br />GO<br /><br />-- Differential Backup<br />USE master<br />GO<br />BACKUP DATABASE Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Differential_Bckup',<br />DIFFERENTIAL,<br />NOFORMAT, NOINIT <br />GO<br /><br />-- Log Backup<br />USE master<br />GO<br />BACKUP LOG Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Log_Backup', <br />NOFORMAT, NOINIT<br />GO<br /><br />--Tail Log Backup<br />USE master<br />GO<br />BACKUP LOG Lab <br />TO DISK = N'C:\Lab.bak' <br />WITH NAME = N'Lab',<br />DESCRIPTION = N'Tail_Log_Backup', <br />NORECOVERY<br />GO<br /><br />-- View and check backup set information<br />RESTORE HEADERONLY FROM DISK = N'C:\Lab.bak'<br />RESTORE FILELISTONLY FROM DISK = N'C:\Lab.bak'<br /><br />-- Restore database according to column position and move datafile<br />USE master<br />GO<br />RESTORE DATABASE Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 1,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE DATABASE Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 2,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE LOG Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH NORECOVERY, FILE = 3,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />RESTORE LOG Lab<br />FROM DISK = N'C:\Lab.bak'<br />WITH RECOVERY, FILE = 4,<br />MOVE 'Lab_DATA' TO 'D:\Lab_DATA.MDF', <br />MOVE 'Lab_FG01' TO 'D:\Lab_FG01.NDF',<br />MOVE 'Lab_FG02' TO 'D:\Lab_FG02.NDF',<br />MOVE 'Lab_FG03' TO 'D:\Lab_FG03.NDF',<br />MOVE 'Lab_LOG' TO 'D:\Lab_LOG.LDF'<br />GO<br /><br />-- Drop database<br />DROP DATABASE Lab<br />-- Execute cmd.exe<br />DEL C:\Lab.bak<br /><br />

SQL Server 2000 的 SQL Mail 提供從 Microsoft SQL Server 傳送和讀取電子郵件的簡易方式，但是 SQL Mail 屬於 MAPI 應用程式，所以伺服器上必須出現 MAPI 子系統。如果要使用 SQL Mail，就必須安裝 MAPI 用戶端，例如 ：Microsoft Outlook 或是 Exchange Server。本文件所提及之技術，伺服器可以不必安裝MAPI 子系統，即可發送電子郵件。

我們可以利用 SQL Server OLE Automation 系統預存程序，來呼叫 CDOSYS 物件模型，並搭配任何支援 SMTP 郵件的郵件伺服器，作為自己的遠端 SMTP 郵件伺服器，來達到寄送電子郵件的功能。

<br />-- If this store procedure exists, drop this store procedure<br />IF (EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_cdosys_sendmail')) DROP PROCEDURE usp_cdosys_sendmail<br />GO<br />-- Create store procedure<br />CREATE PROCEDURE usp_cdosys_sendmail<br /> @From varchar(100) ,<br /> @To varchar(100) ,<br /> @Subject varchar(100)=" ",<br /> @Body varchar(4000) =" "<br />AS<br /> DECLARE @iMsg int<br /> DECLARE @SMTPServer varchar(100)<br /> DECLARE @SendUserName varchar(100)<br /> DECLARE @SendPassword varchar(100)<br /> DECLARE @hr int<br /> DECLARE @source varchar(255)<br /> DECLARE @description varchar(500)<br /> DECLARE @output varchar(1000)<br /> -- This is a sample. Please input your smtp server name or IP address<br /> SET @SMTPServer='mail.com.tw'<br /> -- If smtp server uses authentication, please input your email address and email password. If does not, pass this option.<br /> SET @SendUserName='admin@mail.com.tw '<br /> SET @SendPassword='Password'<br /><br /> -- Create the CDO.Message Object<br /> EXEC sp_OACreate 'CDO.Message', @iMsg OUT<br /> -- Configure a remote SMTP server.<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'<br /> -- Configure the Server Name or IP address.<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@SMTPServer<br /> -- SMTP Authentication<br /> IF NOT (@SendUserName='' and @SendPassword='')<br /> BEGIN<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@SendUserName<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@SendPassword<br /> END<br /> -- Save the configurations to the message object<br /> EXEC sp_OAMethod @iMsg, 'Configuration.Fields.Update', null<br /> -- Set the e-mail parameters<br /> EXEC sp_OASetProperty @iMsg, 'From', @From<br /> EXEC sp_OASetProperty @iMsg, 'To', @To<br /> EXEC sp_OASetProperty @iMsg, 'Subject', @Subject<br /> EXEC sp_OASetProperty @iMsg, 'TextBody', @Body<br /> -- Send mail and do error handling<br /> EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL<br /> IF @hr <> 0<br /> BEGIN<br /> EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT<br /> IF @hr = 0<br /> BEGIN<br /> SELECT @output = ' Description: ' + @description<br /> PRINT @output<br /> END<br /> END<br /> -- Clean up the objects created.<br /> EXEC sp_OADestroy @iMsg<br />GO<br /><br />-- Execute procedure<br />DECLARE @Body varchar(4000)<br />SELECT @Body = 'Your application is rejected. Please contact the system manager.'<br />EXEC usp_cdosys_sendmail 'admin@mail.com.tw','ada@mail.com.tw','System Message',@Body<br /><br />