1. 恢復模式

“恢復模式” 是一種數據庫屬性，它控制如何記錄事務，事務日志是否需要（以及允許）進行備份，
以及可以使用哪些類型的還原操作。有三種恢復模式：簡單恢復模式、完整恢復模式和大容量日志恢復模式。數據庫可以隨時切換為其他恢復模式。 

1.1 簡單恢復模式 Simple

1. Checkpoint with truncate log
2. 在Simple模式下，SQLServer會在每次checkpoint或backup之后自動截斷log，也就是丟棄所有的inactive log records，僅保留用于實例啟動時自動發生的instance recovery所需的少量log。
3. 這樣做的好處是log文件非常小，不需要DBA去維護、備份log。
4. 但壞處也是顯而易見的，就是一旦數據庫出現異常，需要恢復時，最多只能恢復到上一次的備份，無法恢復到最近可用狀態，因為log丟失了。
5. Simple模式主要用于非critical的業務，比如開發庫和測試庫。
6. 如果需要壓縮數據庫日志（Shrink語句），將數據庫模式切換到簡單恢復模式后壓縮率才是最高的，如果你的數據庫在完整恢復模式或大容量日志回復模式下采用日志壓縮，壓縮后的日志大小并不會很理想。

1.2 完整恢復模式 Full

1. Checkpoint without truncate log
2. 在Full模式下，SQL Server不主動截斷log，只有備份log之后，才可以截斷log，否則log文件會一直增大，直到撐爆硬盤，因此需要部署一個job定時備份log。
3. 好處是可以做point-in-time恢復，最大限度的保證數據不丟失，一般用于critical的業務環境里。
4. 缺點就是DBA需要維護log，增加人員成本（其實也就是多了定時備份log這項工作而已）。

1.3 大容量日志模式 Bulk-logged

1. Bulk-logged模式和full模式類似
2. 唯一的不同是針對以下Bulk操作，會產生盡量少的log： 1) Bulk load operations (bcp and BULK INSERT). 2) SELECT INTO. 3) Create/drop/rebuild index
3. 眾所周知，通常bulk操作會產生大量的log，對SQL Server的性能有較大影響，bulk-logged模式的作用就在于降低這種性能影響，并防止log文件過分增長，它的問題是無法point-in-time恢復到包含bulk-logged record的這段時間。
4. Bulk-logged模式的最佳實踐方案是在做bulk操作之前切換到bulk-logged，在bulk操作結束之后馬上切換回full模式。

1.4 如何使用恢復模式

1.5 恢復模式切換建議

在完整恢復模式和大容量日志恢復模式之間切換后：
1. 完成大容量操作之后，立即切換回完整恢復模式。
2. 在從大容量日志恢復模式切換回完整恢復模式后，備份日志。(備份策略保持不變: 繼續執行定期數據庫備份、日志備份和差異備份。)

從簡單恢復模式切換之后：
1. 切換到完整恢復模式或大容量日志恢復模式之后，立即進行完整數據庫備份或差異數據庫備份以啟動日志鏈。(**到完整恢復模式或大容量日志恢復模式的切換僅在第一個數據備份之后才生效**。)
2. 計劃安排定期日志備份并相應地更新還原計劃。(**備份日志，如果不經常備份日志，事務日志可能會擴展直到占滿磁盤空間**！)

切換到簡單恢復模式之后：
1. 中斷用于備份事務日志的所有計劃作業。
2. 確保定期執行數據庫備份。備份數據庫對于保護數據和截斷事務日志的不活動部分是基本操作。

2. 備份設備

2.1 物理備份設備

2.2 邏輯備份設備

邏輯設備是指向特定物理備份設備（磁盤文件或磁帶機）的用戶定義名稱。將備份寫入備份設備后，便會初始化物理設備。

3. 本地備份

3.1 三種備份類型

1. 完整備份 (full backup) ： 完整備份
2. 差異備份 (differential backup) :  完整備份后的以來發生更改的數據。
3. 日志備份 (log backup) : 包括以前日志備份中未備份的所有日志記錄的事務日志備份。 （完整恢復模式） （定期的進行日志備份，日志備份可以截斷事務，可以使得空間重用。）

3.2 基本語法 (Transact-SQL)

3.3 完整備份

3.3.1 如何創建完整備份（Transact-SQL示例）

3.3.2 powershell 完整備份

3.3.3 權限

1. 默認情況下，為 sysadmin 固定服務器角色以及 db_owner 和 db_backupoperator 固定數據庫角色的成員授予 BACKUP DATABASE 和 BACKUP LOG 權限 。
2. 備份設備的物理文件的所有權和權限問題可能會妨礙備份操作。 SQL Server 服務應對設備進行讀寫。 運行 SQL Server 服務所用的帳戶必須擁有對備份設備的寫入權限。 但是，用于在系統表中添加備份設備條目的 sp_addumpdevice 并不檢查文件訪問權限。 除非使用備份或嘗試還原，否則備份設備的物理文件可能不會出現問題。（**注意邏輯設備對應的磁盤權限**）

3.4 差異備份

3.4.1 如何創建差異備份（Transact-SQL示例）

3.4.2 powershell 差異備份

3.4.3 權限

1. BACKUP DATABASE 和 BACKUP LOG 權限默認為 sysadmin 固定服務器角色以及 db_owner 和 db_backupoperator 固定數據庫角色。
2. 備份設備的物理文件的所有權和權限問題將會妨礙備份操作。 SQL Server 需能夠讀取和寫入設備；運行 SQL Server 服務的帳戶必須具有寫入權限。 但是，用于在系統表中為備份設備添加項目的 sp_addumpdevice不 檢查文件訪問權限。 在你因嘗試備份或還原而訪問物理資源之前，備份設備物理文件中的權限問題并不明顯。（**注意邏輯設備對應的磁盤權限**）

3.5 事務日志備份

3.5.1 如何創建事務日志備份（Transact-SQL示例）

3.5.2 powershell 事務日志備份

3.6 提升備份效率的手段

3.6.1 備份到多個文件提高備份效率

1. 備份到多個磁盤，可以充分利用磁盤IO，減少備份時間。
2. 另外如果單個磁盤空間不足的話，備份到多個文件可以使備份文件變小，解決磁盤空間問題，另外通過網絡移動到其他地方也比較方便。
3. 備份到多個文件可以SQL Server使用多個備份線程，提供效率。

3.7 查詢備份恢復歷史

3.7.1 TSQL 查詢備份歷史

3.7.2 powershell 查詢備份歷史

3.7.3 TSQL查詢恢復歷史

4. 還原

4.1 還原順序

1. 計劃還原順序
   - 創建數據庫的結尾日志備份（如果可以）
   - 確定目標恢復點。（目標恢復點可以是事務日志備份中的任何時間點或標記）
   - 確定要執行的還原類型。
   - 標識您需要的備份，并確保必要的介質集和備份設備可用。
2. 執行還原
   - 還原完整備份
   - 還原基于這些完整備份的差異備份
   - 通過按順序還原日志備份、完成包含恢復點的備份來前滾數據庫。是否必須應用所有日志備份取決于日志備份包含什么樣的目標恢復點，如下所示：
       （1） 如果恢復點是故障點，則必須還原自上一次還原數據（完整或差異）備份以來創建的所有日志備份。
       （2）對于時點還原，您可能不需要最新的日志備份。只需要恢復到包含時間點的那個日志備份文件并指定時間即可。

4.2 還原到故障點（完整恢復模式）

還原到原庫或其他庫或者新實例都可以。

 4.2.1 典型的還原順序圖解及步驟

步驟如下：

1. 備份活動事務日志（稱為日志尾部）。 此操作將創建結尾日志備份。 如果活動事務日志不可用，則該日志部分的所有事務都將丟失。(對應上圖t10及紅框2，因為最后一個日志備份在t9時刻，則**t9至故障點t10階段的事務日志是沒有備份的，因此這里需要備份這段結尾日志，不備份的話這部分日志就丟失了，數據丟失，除非這段時間沒有事務及數據變更**)。
2. 還原最新完整數據庫備份而不恢復數據庫 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。
3. 如果存在差異備份，則還原最新的差異備份而不恢復數據庫 (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY)。 （**還原最新差異備份可減少必須還原的日志備份數。即完整備份和差異備份中間的日志備份是不需要還原的，因為后面的差異備份包含了前面的事務日志備份。另外如果有多個差異備份，則按順序恢復**）
4. 還原備份后創建的第一個事務日志備份開始，使用 NORECOVERY 依次還原日志。另外，**如果備份了結尾日志，也需要還原這個結尾日志備份**。
5. 恢復數據庫 (RESTORE DATABASE database_name WITH RECOVERY)。 此步驟也可以與還原上一次日志備份結合使用。

提示：

1. 有點類似mysql xtrabackup 備份還原，最后一個增備apply-log。恢復事務日志代表重做，最后一步代表回滾。回滾后，數據庫將進入聯機狀態，不能再將其他事務日志備份應用到數據庫。
2. WITH NORECOVERY ： 讓庫處于還原狀態（還原狀態時庫database不可以使用，因為這個時候數據是不完整的，不讓使用和更新）。
3. WITH RECOVERY ： 讓應用進入恢復后狀態（此時，庫database是可用了）。

4.2.2 基本語法（Transact-SQL示例）

4.2.3 實際操作（Transact-SQL示例）

4.3 還原到時間點

在完整恢復模式下，完整的數據庫還原通常可恢復到日志備份中的某個時間點、標記的事務或 LSN。 但是，**在大容量日志恢復模式下，如果日志備份包含大容量更改，則不能進行時點恢復**。

4.3.1 實操實際操作（Transact-SQL示例）

4.4 powershell 還原

4.5 權限

1. 如果不存在要還原的數據庫，則用戶必須有 CREATE DATABASE 權限才能執行 RESTORE。 如果數據庫存在，則 RESTORE 權限默認授予 sysadmin 和 dbcreator 固定服務器角色成員以及數據庫的所有者 (dbo)（對于 FROM DATABASE_SNAPSHOT 選項，數據庫始終存在）。
2. RESTORE 權限被授予那些成員身份信息始終可由服務器使用的角色。 因為只有在固定數據庫可以訪問且沒有損壞時（在執行 RESTORE 時并不會總是這樣）才能檢查固定數據庫角色成員身份，所以 db_owner 固定數據庫角色成員沒有 RESTORE 權限。
3. 文件權限

 4.6 還原到另外一個數據庫名（復制數據庫）

5. 其他備份還原相關

5.1 結尾日志備份

結尾日志備份說的是還原數據庫前的最后一個事務日志備份（日志尾部）。對于使用完整恢復模式或大容量日志恢復模式的數據庫，通常需要在開始還原數據庫前備份日志尾部。 在對日志傳送配置進行故障轉移之前，還應當備份主數據庫的日志尾部。 將結尾日志備份作為恢復數據庫之前的最后一個日志備份還原可以防止失敗后丟失工作。

需要結尾日志備份的方案（建議在以下場景中執行結尾日志備份）：

1. 如果數據庫處于聯機狀態并且您計劃對數據庫執行還原操作，則從備份日志結尾開始。 若要避免聯機數據庫出錯，必須使用 ... BACKUP Transact-SQL 語句的 WITH NORECOVERY 選項。
2. 如果數據庫處于脫機狀態而無法啟動，則需要還原數據庫，從備份日志結尾開始。 由于此時不會發生任何事務，因此 WITH NORECOVERY 是可選的。
3. 如果數據庫損壞，則嘗試使用 BACKUP 語句的 WITH CONTINUE_AFTER_ERROR 選項執行結尾日志備份。

5.2 壓縮備份、還原

5.3 加密備份、還原

6. 備份恢復答疑及踩坑記錄

6.1. 事務日志不斷增長、空間滿

1. 原因1： 數據庫是完整恢復模式，但是并沒有定期的進行日志備份，導致事務日志不斷膨脹。
解決方法： 定期進行日志備份

2. 原因2： 有事務長時間沒有提交
解決方法： 查找出已經運行完成但沒有提交的事務，kill掉此事務即可

3. 原因3： 大事務正在運行（事務很大，一直不停的在記錄大量的日志，導致日志增大）
解決辦法： 盡量優化業務

6.2 完整備份包含事務日志備份嗎？

數據庫完整備份將備份整個數據庫。 還包括部分的事務日志，以便在還原完整數據庫備份后可以恢復完整數據庫。其實差異備份也是如此。完整或差異備份需要日志來將數據庫還原到當完整或差異備份結束時的事務一致性狀態。所以，完整備份或差異備份包含部分事務日志備份。準確的說是從完整備份開始到結束這段時間的事務日志備份。

6.3 完整備份會截斷事務日志嗎？

首先，我們先搞清楚一個概念，截斷日志（log truncating）和日志清理（log clearing）其實是同一件事情，它們表示事務日志的一部分被標記為不再需要，可以覆蓋重復使用了（有點類似Oracle下的redo log歸檔后，可以被重新覆蓋了）。在完整或大容量事務日志恢復模式下，只有備份日志才會清除日志。我們知道完整備份會包含事務日志備份，但是它確實不會截斷日志(清除日志)，跟多詳細細節參考“Misconceptions around the log and log backups: how to convince yourself”

6.4 簡單恢復模式下能做事務日志備份嗎？

不行。簡單的恢復模式下僅允許完整備份和差異數據庫備份，并且沒有進行事務日志備份的機會。 在簡單恢復模型中創建檢查點時，將從事務日志中刪除所有已提交的事務。

6.5 恢復時報錯 ： Exclusive access could not be obtained because the database is in use

問題： 當前庫還有活動連接在使用中

6.6 恢復時報錯 ： This differential backup cannot be restored because the database has not been restored to the correct earlier state.

問題 ： 說明還原的順序不對，還原差異備份一定要基于最新的完整備份。

解決方法： 還原差異前選擇最近一個完整備份。

6.7 恢復完成，目標庫顯示“正在還原”

原因：事務未回滾，需要手動回滾事務

解決方法： restore database [db_name] with recovery

6.8 恢復備份文件到另一個庫時報錯， Logical file 'yywtest' is not part of database 'yywtest'. Use RESTORE FILELISTONLY to list the logical file names.

原因： 邏輯文件名和物理備份文件不匹配。

解決方法： 通過 RESTORE FILELISTONLY FROM DISK = N'D:\baktest\SQLTestDB\SQLTestDB-Full-1.trn' 獲取邏輯名，然后還原的時候用這個語句查出來的邏輯名替換。