sql server 備份與恢複系列五 完整模式下的備份與還原

一.概述

　　前面介紹了簡單復原模式和大容量復原模式，這篇繼續寫完整復原模式下的備份與還原。在完整復原模式裡最大的優點是只要能成功備份尾日誌，就可以還原到記錄備份內包含的任何時點("時間點復原")。當然對比前二種模式它是犧牲了磁碟I/O效能。

復原模式	備份策略	資料安全性	I/O效能
簡單恢複	完整備份+差異備份	安全最差。最後一次備份之後，所有資料操作丟失。	最優
大容量恢複	完整備份+差異備份+記錄備份	折中。大量操作有丟失風險。尾記錄備份失敗。最後一次備份之後，所有資料操作丟失	折中
完整恢複	完整備份+差異備份+記錄備份	相比上面二種最安全。尾記錄備份失敗。最後一次備份之後，所有資料操作丟失	最差

　　在完整復原模式下，最常見的備份策略，如所示：

二. 備份

　　在前章中講到了大容量復原模式下的備份。備份策略與大容量模式是一樣的，同樣是完整備份+差異備份+記錄備份。這裡要突出點是：當誤操作發生後，如何還原到誤操作之前的一分鐘，找出誤操作之前的資料。
在"sql server 記錄檔結構及誤操作資料找回"中有介紹誤操作資料找回，但是基於第三方工具ApexSQL Log。雖然該工具方便,但要收費喲。

　　我這裡有一個BackupTest庫，庫裡有個Employees表

use master--設定完全模式ALTER DATABASE BackupTest SET  RECOVERY FULL  --建立備份裝置(有就不要執行)use masterexec sp_addumpdevice 'disk', 'BackupTestDevice','F:\SqlService\backup\BackupTestBackup.bak'go--做一次完整備份到備份裝置中(備份基準)backup database  BackupTest to BackupTestDevice--新增資料insert BackupTest.dbo.Employees values('湖南長沙')insert BackupTest.dbo.Employees values('湖南湘潭')--記錄備份backup log BackupTest to BackupTestDevice

 備份組如下所示：

-- 誤操作發生, 忘記加where條件,操作時間是:2018-8-12 10:55  delete from BackupTest.dbo.Employees 

三.還原(1)

　　當誤操作發生後，是需要找管理員來進行資料還原。 如果資料庫太大，還原是需要很長時間（注意使用副本，不要使用生產庫）。 這種情況下就需要等待了。 避免的方法：(1)是做sql審核，不在Managemnet studio裡直接操作，避免此類事情發生.(2)是使用粒度更小的備份方式，但相應的複雜些。

--步驟1 備份尾日誌use mastergobackup log BackupTest to BackupTestDevice with norecovery 

go--步驟2 從備份恢複一個全備份 ,norecovery(正在還原...)不可讀寫. file指備份組位置號restore database BackupTest from BackupTestDevice with file=19, norecovery --事務不恢複--步驟3 restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事務不恢複--步驟4 用stopat恢複到10:54restore log BackupTest from BackupTestDevice  with file=21, stopat='2018/8/12 10:54', recovery --事務恢複

--資料又回來了select * from  BackupTest.dbo.Employees 

　　

四.還原(2)

　　在前面介紹中，有講過，完整復原模式切換到大容量模式，日誌鏈是不會中斷。下面來驗證

--從完整復原模式切換到大容量模式ALTER DATABASE BackupTest SET  RECOVERY bulk_logged -- 新增insert BackupTest.dbo.Employees values('湖南株洲')--記錄備份backup log BackupTest to BackupTestDevice-- 刪除delete from BackupTest.dbo.Employees 

-- 尾日誌backup log BackupTest to BackupTestDevice with norecovery 

　備份組如下所示，記錄檔ID：22是在大容量模式下備份的，23是尾日誌

restore database BackupTest from BackupTestDevice with file=19, norecovery --事務不恢複restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事務不恢複restore log BackupTest from BackupTestDevice  with file=21,  norecovery --事務不恢複restore log BackupTest from BackupTestDevice  with file=22,  recovery 

　　當日誌還原到檔案ID：22時,報錯，如所示

 　　跳過檔案ID：22, 使用23來提交事務，也會報錯，如下所示：

restore log BackupTest from BackupTestDevice  with file=23,  recovery

 　　經過測試，還原失敗，錯誤是指：與上一次還原到指定時間點有關係。

　　下面在測試一個新庫TestFULLToBulk

--設定完全模式ALTER DATABASE TestFULLToBulk SET  RECOVERY FULL  --做一次完整備份到備份裝置中(備份基準)backup database  TestFULLToBulk to BackupTestDeviceinsert TestFULLToBulk.dbo.product values('湖南株洲')--記錄備份backup log TestFULLToBulk to BackupTestDevice--設定大容量ALTER DATABASE TestFULLToBulk SET RECOVERY bulk_logged   insert TestFULLToBulk.dbo.product values('湖南湘潭')--記錄備份backup log TestFULLToBulk to BackupTestDevice

　　備份組如下：檔案ID28是在大容量下進行的備份

　　

backup log TestFULLToBulk to BackupTestDevice with norecovery gorestore database TestFULLToBulk from BackupTestDevice with file=26, norecovery gorestore log TestFULLToBulk from BackupTestDevice  with file=27,  norecovery gorestore log TestFULLToBulk from BackupTestDevice  with file=28,  recovery 

　　上面還原成功，證明了完整復原模式切換到大容量模式，日誌鏈是不會中斷。