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

-Advertisement-

一. 概述在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

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

您的分享是我們最大的動力!