sql server 備份與恢複系列四 大容量模式下的備份與還原

原文: sql server 備份與恢複系列四 大容量模式下的備份與還原

一. 概述

　　在sql server 備份與恢複系列的第一篇裡，有講到大容量模式下備份與還原的相關知識。這篇重點來示範在大容量模式下常用的備份與還原模式“完整備份+差異備份+日志備份”。 在大容量恢複模式下，特别要注意的是在什麼情況下會導緻資料還原丢失風險，帶着這個問題，來進行示範說明。備份政策如下圖所示：

二.備份

　　　　我這裡有TestBulkLogged庫，庫裡建立了一個product空表。備份SQL語句如下所示：

use master
-- 設定大容量模式
ALTER DATABASE TestBulkLogged SET RECOVERY bulk_logged

-- 做一次完整備份到備份裝置中(備份基準) 
backup database  TestBulkLogged to BackupTestDevice

-- 新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第一次新增資料',9708,'IT')

-- 做一次日志備份
backup log   TestBulkLogged to BackupTestDevice

-- 批量插入(5998 行受影響)
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand)
select model,upbymemberid,brand from test.dbo.product

-- 做二次日志備份
backup log   TestBulkLogged to BackupTestDevice

-- 第二次日志備份後的新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第二次新增資料',9708,'IT')

-- 做差異備份
backup database  TestBulkLogged to BackupTestDevice with differential 

-- 全部删除(6000 行受影響)
delete from TestBulkLogged.dbo.product      

　　檢視備份集清單如下圖所示：

三. 還原(1)批量插入的是否會丢失

　　通過還原檢視批量插入操作是否丢失，在備份尾日志時如果報錯, 資訊如下："因為資料庫正在使用，是以無法獲得對資料庫的獨占通路權" 需要将庫設定成單使用者模式

use master      

-- 先還原完整備份 ,norecovery(正在還原...)不可讀寫. file指備份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery       

　　　　

   在大容量模式下還原時，sql server會檢測你是否進行了尾日志備份，也是確定最後一次日志備份後，所做的資料操作在還原後不丢失。（如果尾日志備份失敗，則丢失資料）。下面先備份一下尾日志, 使用norecovery 暫不送出

-- 尾日志備份
backup log TestBulkLogged to BackupTestDevice with norecovery       

 上圖備份了尾日志後，備份集裡多出了一個檔案号14, 下面在重新還原完整備份

-- (重新)從備份恢複一個全備份 ,norecovery(正在還原...)不可讀寫. file指備份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery       

-- 恢複到日志檔案11  
restore database TestBulkLogged from BackupTestDevice  with file=11, norecovery

-- 恢複到日志檔案12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery      

 接下來我們來查詢下庫中的product表，檢視資料是否全部恢複。

-- 查詢大批量操作的資料,是否已還原出來
select * from TestBulkLogged.dbo.product      

　　

　　結論：通過上圖我們可以了解到，第一次和第二次做的日志備份都完美的還原了過來。 大批量插入操作也得到了還原。證明在大容量模式下，大批量操作的資料, 還原恢複可能存在丢失的風險，但不一定會丢失掉。

 四. 還原(2)打斷日志鍊

　　在前面講述事務日志時提到了, 事務日志鍊LSN, 在還原的時候必須要保持事務鍊的順序，依次的還原。 下面示範跳過日志鍊檔案ID：11 ,直接還原日志鍊檔案ID:12。

-- 尾日志備份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 從備份恢複一個全備份 ,norecovery(正在還原...)不可讀寫. file指備份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 跳過日志檔案11,恢複到日志檔案12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery      

　　結論：如果隻有（完整備份和事務日志備份）， 在還原時，事務日志必須保持LSN順序,依次還原，否則還原失敗就會丢失資料。

五. 還原(3) 基于差異備份下的日志還原

　　在生産環境中，由于日志檔案備份頻繁，導緻日志檔案太多，如果按日志檔案一個一個來還原，需要大量時間和精力。下面示範直接從差異備份還原開始，看後面的日志檔案是否能還原成功。

-- 尾日志備份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 從備份恢複一個全備份 ,norecovery(正在還原...)不可讀寫. file指備份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢複到差異備份檔案13. 跳過日志檔案11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, recovery      

   上面還原是跳過了日志檔案，直接使用差異備份檔案還原。我們來檢視下表中的資料,會發現差異備份完全可以還原正确成功。

下面是差異備份與日志備份組合來還原，結論是日志檔案不需要一個一個來還原，可以直接定位到，一個差異備份來還原，再還原，之後的日志檔案。

-- 尾日志備份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 從備份恢複一個全備份 ,norecovery(正在還原...)不可讀寫. file指備份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢複到差異備份檔案13. 跳過日志檔案11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, norecovery

-- 恢複到日志檔案14 
restore database TestBulkLogged from BackupTestDevice  with file=14, recovery      

　　 結論：有了差異備份，在還原時就節省了很多還原時間和精力。可以在完整備份的基準内，直接選擇最後一次的差異備份加上之後的日志備份來還原。