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

標籤：單使用者   str   ima   base   color   style   std   否則   isp   

原文: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

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

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