---

title: MSSQL · 最佳實踐 · SQL Server備份策略

author: fengyi

摘要

在上一期月報中我們分享了SQL Server三種常見的備份技術及工作方式，本期月報將分享如何充分利用三者的優點來制定SQL Server資料庫的備份和還原策略以達到資料庫快速災難恢復能力。

上期月報：MSSQL · 最佳實踐 · SQL Server三種常見備份

三個術語

在詳細介紹SQL Server的災備策略之前，我們先簡要介紹三個重要的術語：

• RTO (Recovery Time Objective)恢復時間目標，是指出現災難後多長時間能成功恢復資料庫，即企業可容許服務中斷的最大時間長度。比如說災難發生後一天內恢復成功，則RTO值就是二十四小時；
• RPO (Recovery Point Objective)恢復點目標，是指服務恢復後，恢復回來的資料所對應的最新時間點。比如企業每天凌晨零晨進行完全備份一次，那麼這個全備恢復回來的系統資料只會是最近災難發生當天那個凌晨零時的資料；
• ERT(Estimated Recovery Time)預估恢復時間，是指根據備份鏈路的長度和備份檔案的大小以及裝置的還原效率來估算的服務恢復時間。
  從以上的三個術語解釋來看，最優的災備方案是RTO極小，即出現故障能夠立馬恢復資料；RPO無線接近故障時間點，即最少的資料丟失；ERT最小，即可快速恢復服務。但是，現實場景中的災備方案往往很難達到如此優化的方案。

制定災備策略

以上三個術語是衡量災備方案和還原策略優劣的重要指標，我們的災備策略的目標也是無限的靠近RTO、RPO和ERT的最優值。以下我們列舉一個典型的災備場景來分析和解答：
假設某個企業對SQL Server資料庫DBA提出的災難恢復要求是資料丟失不超過一小時（RPO不超過一小時），在儘可能短的時間內（RTO儘可能短）恢復應用資料庫服務，且災備策略必須具備任意時間點還原的能力。
綜合上一期月報分享，我們先拋開災備策略的優劣來看，我們看看三種典型的災備策略方案是否可以實現RPO？

• 每個小時一次完全備份：備份檔案過大，備份還原效率低下，這種方案無法實現任意時間點的還原；
• 每天一個完全備份 + 每小時一個日誌備份：解決了備份檔案過大和效率問題，也可以實現任意時間點還原，但是拉長了日誌還原鏈條；
• 每天一個完全備份 + 每六個小時一個差異備份 + 每小時一個日誌備份：具備任意時間點還原的能力，綜合了備份檔案大小、效率和備份鏈條長度。
  從這個分析來看，也恰好應證了上一期的月報中的結論，即：完全備份集是所有備份的基礎，但資料量大且備份耗時；事務日誌備份集相對較小且快速，但會拉長備份檔案還原鏈條，增大還原時間開銷；差異備份解決了事務日誌備份鏈條過長的問題。

時間點恢復

我們假裝置份資料增量為每小時1GB，初始完全備份大小為100GB，按照時間維度計算每小時產生的備份集大小，統計如下：

典型場景

假設我們非常重要的訂單資料庫，在13:30被人為的錯誤刪除掉了，災備系統在14:00進行了一個事務日誌備份。那麼，這個事務日誌備份對我們業務的災難恢復就非常關鍵和重要了，它使得我們有能力將資料庫還原到13:29:59這個時間點。如此，我們只會丟失13:30 – 14:00之間的這半個小時的資料（實際上我們也有能力找回13:30 – 14:00）。但是，如果沒有14:00這個事務日誌備份檔案，但存在13:00的事務日誌備份檔案的話，我們的系統資料會丟失13:00 – 14:00之間這一個小時的資料，一個小時的資料丟失是公司不被允許的。場景如下圖展示：

模擬備份策略

我們可以使用以下方法模擬災備方案和災難恢復的步驟：

• 第一步：建立測試資料庫並修改為FULL模式
• 第二步：建立一個完全備份
• 第三步：每一個小時做一次事務日誌備份
• 第四步：每六個小時做一個差異備份
  詳細的模擬方法和語句如下所示：

-- Create testing DB
IF DB_ID(`TestDR`) IS NULL
    CREATE DATABASE TestDR;
GO

-- Change Database to FULL Recovery Mode 
-- for time point recovery supporting
ALTER DATABASE [TestDR] SET RECOVERY FULL WITH NO_WAIT
GO

USE TestDR
GO

-- Create Testing Table
IF OBJECT_ID(`dbo.tb_DR`, `U`) IS NOT NULL
    DROP TABLE dbo.tb_DR
GO

CREATE TABLE dbo.tb_DR
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Comment VARCHAR(100) NULL,
    Indate DATETIME NOT NULL DEFAULT(GETDATE())
);
GO

USE TestDR
GO
-- Init data
INSERT INTO dbo.tb_DR(Comment)
SELECT `Full Backup @ 00:00`;
-- Take Full Backup
BACKUP DATABASE [TestDR] TO 
DISK =N`C:TempTestDR_20171217@00:00_FULL.bak` WITH COMPRESSION,INIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 01:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@01:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 02:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@02:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 03:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@03:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 04:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@04:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 05:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@05:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;


INSERT INTO dbo.tb_DR(Comment)
SELECT `DIFF Backup @ 06:00`;
-- Take DIFF Backup
BACKUP DATABASE [TestDR] TO 
DISK =N`C:TempTestDR_20171217@06:00_DIFF.bak` WITH DIFFERENTIAL,COMPRESSION,NOINIT,STATS=5;



INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 07:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@07:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 08:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@08:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 09:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@09:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 10:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@10:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 11:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@11:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;


INSERT INTO dbo.tb_DR(Comment)
SELECT `DIFF Backup @ 12:00`;
-- Take DIFF Backup
BACKUP DATABASE [TestDR] TO 
DISK =N`C:TempTestDR_20171217@12:00_DIFF.bak` WITH DIFFERENTIAL,COMPRESSION,NOINIT,STATS=5;


INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 13:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@13:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

-- This record is similate for point time recovery
INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 13:29:59`;

WAITFOR DELAY `00:00:02`

INSERT INTO dbo.tb_DR(Comment)
SELECT `Transaction Log Backup @ 14:00`;
-- Take TRN Backup
BACKUP LOG [TestDR] TO 
DISK =N`C:TempTestDR_20171217@14:00_LOG.trn` WITH COMPRESSION,NOINIT,STATS=5;

-- Query Data
SELECT * FROM dbo.tb_DR;

我們看看測試表的資料情況，方框選中的這條資料是需要我們恢復出來的：

我們也可以再次檢查資料庫備份歷史記錄，來確保災備資訊準確性：

SELECT
bs.database_name AS `Database Name`,
bs.backup_start_date AS `Backup Start`,
bs.backup_finish_date AS `Backup Finished`,
DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS `Duration (min)`,
bmf.physical_device_name AS `Backup File`,
CASE 
    WHEN bs.[type] = `D` THEN `Full Backup` 
    WHEN bs.[type] = `I` THEN `Differential Database` 
    WHEN bs.[type] = `L` THEN `Log` 
    WHEN bs.[type] = `F` THEN `File/Filegroup` 
    WHEN bs.[type] = `G` THEN `Differential File`
    WHEN bs.[type] = `P` THEN `Partial`  
    WHEN bs.[type] = `Q` THEN `Differential partial` 
END
AS `Backup Type`
FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK)
    INNER JOIN msdb..backupset bs WITH(NOLOCK)
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = `TestDR`
ORDER BY bs.backup_start_date ASC

查詢的災備歷史記錄展示如下：

從這個備份歷史記錄來看，和我們的測試表中的資料是吻合且對應起來的。

災難恢復步驟

接下來，我們需要根據TestDR資料庫的備份檔案，將資料庫恢復到模擬時間點2017-12-17 23:04:45.130（即真實場景中的發生人為操作失誤的時間點13:30），為了包含ID為15的這條資料，我們就恢復到2017-12-17 23:04:46.130時間點即可，然後檢檢視看ID等於15的這條記錄是否存在，如果這條記錄存在，說明我們備份和還原策略工作正常，否則無法實現公司的要求。為了試驗的目的，我們先把TestDR資料庫刪除掉（真實環境，請不要隨意刪除資料庫，這很危險）：

-- for testing, drop db first.
USE [master]
GO
ALTER DATABASE [TestDR] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [TestDR]
GO

恢復方案一：全備 + 日誌備份

為了實現災難恢復，我們需要先把完全備份檔案恢復，然後一個接一個的事務日誌備份按時間升序恢復，在最後一個事務日誌恢復的時候，使用STOPAT關鍵字恢復到時間點並把資料庫Recovery回來帶上線，詳細的程式碼如下：

USE [master]
GO
-- restore from full backup
RESTORE DATABASE TestDR
FROM DISK = `C:TempTestDR_20171217@00:00_FULL.bak` WITH NORECOVERY, REPLACE

-- restore from log backup
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@01:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@02:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@03:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@04:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@05:00_LOG.trn` WITH NORECOVERY

-- skip diff backup at 06:00
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@07:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@08:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@09:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@10:00_LOG.trn` WITH NORECOVERY
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@11:00_LOG.trn` WITH NORECOVERY

-- skip diff backup at 12:00
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@13:00_LOG.trn` WITH NORECOVERY

-- restore from log and stop at 2017-12-17 23:04:46.130
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@14:00_LOG.trn` WITH STOPAT = `2017-12-17 23:04:46.130`, RECOVERY

-- Double check test data
USE TestDR
GO
SELECT * FROM dbo.tb_DR

從測試表中的資料展示來看，我們已經成功的將ID為15的這條資料還原回來，即發生人為失誤導致的資料丟失（災難）已經恢復回來了。

細心的你一定發現了這個恢復方案，使用的是完全備份 + 很多個事務日誌備份來恢復資料的，這種方案的恢復鏈條十分冗長，在這裡，恢復到第13個備份檔案才找回了我們想要的資料。有沒有更為簡單，恢復更為簡潔的災難恢復方案呢？請看恢復方案二。

恢復方案二：全備 + 差備 + 日誌備份

為了解決完全備份 + 日誌備份恢復鏈條冗長的問題，我們接下來採取一種更為簡潔的恢復方案，即採用完全備份 + 差異備份 + 事務日誌備份的方法來實現災難恢復，方法如下：

--=========FULL + DIFF + TRN LOG
USE [master]
GO
-- restore from full backup
RESTORE DATABASE TestDR
FROM DISK = `C:TempTestDR_20171217@00:00_FULL.bak` WITH NORECOVERY, REPLACE

-- restore from diff backup
RESTORE DATABASE TestDR 
FROM DISK = `C:TempTestDR_20171217@12:00_DIFF.bak` WITH NORECOVERY

-- restore from trn log
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@13:00_LOG.trn` WITH NORECOVERY

-- restore from log and stop at 2017-12-17 23:04:46.130
RESTORE LOG TestDR 
FROM DISK = `C:TempTestDR_20171217@14:00_LOG.trn` WITH STOPAT = `2017-12-17 23:04:46.130`, RECOVERY

-- Double check test data
USE TestDR
GO
SELECT * FROM dbo.tb_DR

從這個災難恢復鏈路來看，將災難恢復的步驟從13個備份檔案減少到4個備份檔案，鏈路縮短，方法變得更為簡潔快速。當然同樣可以實現相同的災難恢復效果，滿足公司的對資料RPO的要求。

恢復方案三：使用SSMS

當然災難恢復的方法除了使用指令碼以外，微軟的SSMS工具通過IDE UI操作也是可以達到相同的效果，可以實現相同的功能，方法如下：右鍵點選你需要還原的資料庫 => Tasks => Restore => Database，如下如所示：

選擇Timeline => Specific date and time => 設定你需要還原到的時間點（這裡選擇2017-12-17 23:04:46） => 確定。

時間點恢復還原時間消耗取決於你資料庫備份檔案的大小，在我的例子中，一會功夫，就已經還原好你想要的資料庫了。

最後總結

本期月報是繼前一個月分享SQL Server三種常見的備份技術後的深入，詳細講解了如何制定災備策略來滿足企業對災難恢復能力的要求，並以一個具體的例子來詳細闡述了SQL Server災備的策略和災難恢復的方法，使企業在資料庫災難發生時，資料損失最小化。但是，這裡還是有一個疑問暫時留給讀者：為什麼我們可以使用多種災難恢復（我們這裡只談到了兩種，實際上還有其他方法）的方法呢？到底底層的原理是什麼的？預知後事如何，我們下期月報分享。

參考

典型場景中的場景圖

Point-in-time recovery