第一篇——第一文 SQL Server 備份基礎

當看這篇文章之前，請先給你的所有重要的庫做一次完整Database Backup。下面正式開始備份還原的旅程。

原文出處： http://blog.csdn.net/dba_huangzj/article/details/22683687前言

• 為什麼要備份？理由很簡單——為了還原/恢複。當然，如果不備份，還可以通過磁碟恢複來找回丟失的檔案，不過SQL Server很生氣，後果很嚴重。到時候你就知道為什麼先叫你備份一次再開始看文章了。∩__∩。本系列將介紹SQL Server所有可用的備份還原功能，並儘可能用執行個體說話。
• 什麼是備份？SQL Server基於Windows，以檔案形式存放資料，所以備份就是Windows上SQL Server相關檔案的一個某個時間點的副本。根據備份類型的不同，副本的種類和內容也有不同。
• 備份類型有哪些？SQL Server 目前版本中，可用的備份類型有：完整Database Backup、差異Database Backup、交易記錄備份（後稱記錄備份）、檔案和檔案組備份、部份備份，根據SQL Server版本不同，有些備份類型不支援，另外根據復原模式的不同，某些備份類型也不支援。

什麼是復原模式？

很多人只把關注點放在備份上面，而沒有在意復原模式，其實所有的備份都應該從復原模式作為切入點。復原模式實際上是一個控製備份還原的行為的資料庫層級選項。SQL Server 在當前所有發布版本中只有三種復原模式：簡單復原模式（後面簡稱簡單模式），大量記錄復原模式（後面簡稱大容量模式），完整復原模式（後稱完整模式）。

本文從復原模式開始，提醒一下，絕大部分的專業屬雩都會陸續解釋，如果讀者有不明白，可以繼續往下看或者上網搜尋：

• 簡單模式，Simple recovery model：某些操作可以被最小日誌化。這種模式下，不支援記錄備份、時間點恢複和頁恢複。且檔案恢複功能僅限於次要資料檔案中的唯讀檔案。
• 大容量記錄模式，Bulk-logged recovery model：和完整模式類似，有時候可以理解為完整模式於簡單模式的過渡模式。這種模式對某些大容量操作進行最小日誌化，支援完整備份中的備份還原策略，但是由於某些操作被最小日誌化，所以不能保證時間點恢複。
• 完整模式，Full recovery model： 在這個模式下，所以操作都被完整記錄下來，並且支援所有類型的備份還原策略。

預設情況下，新庫會繼承Model庫的配置，包括復原模式，也就是FULL模式。可以在建立或日常使用過程中修改，並且不需要重啟服務。復原模式最重要的區別在於對待日誌的行為。

簡單模式：

是三種模式中最容易管理的，可以進行完整，差異和檔案備份，但是不能做記錄備份。在這種模式下，每當Checkpoint 進程發生時，會自動把記錄檔中不活動的日誌（在記錄備份一文介紹）寫入資料檔案，寫入後，對應的記錄檔中的空間就可供新事務使用，注意這種空間重用或者截斷並不自動減少記錄檔的物理大小，如果需要減少空間，需要使用DBCC SHRINKFILE/DATABASE等命令實現。讓日誌空間重用的過程叫做截斷。在簡單模式下這個過程稱為自動截斷(auto-truncate)。在這種模式下，日誌通常不需要管理，但是對於單個的大事務，記錄檔可能會增長得很快，這種情況下最好把批處理降為小的批。簡單模式最主要的限制是不能進行記錄備份，也就是說無法進行還原時間點。在一些測試，開發或者SLA要求不嚴格的環境下，可以使用這種模式。

完整模式：

這種模式下，所有資料庫操作都被完整地記錄在日誌中，2008出現某些操作在這種模式下也還是最小化日誌。並且不是自動截斷。它支援任何備份還原策略，特別是還原時間點，在日誌還原一章介紹。即使發生Checkpoint ，不活動的事務也不會截斷到資料檔案中。唯一能控制記錄檔的只有記錄備份，所以這種模式下記錄備份極其重要，一方面提供還原時間點，另外一方面控制記錄檔大小。

記錄檔會完整儲存自上一次記錄備份後的事務。使用copy_only或者no_truncate選項均不會截斷日誌。

大容量日誌：

這種模式是最少用到的，某些操作會被最小日誌化，包含：

• 使用bcp進行匯入
• bulk insert
• insert select *from openrowset(bulk )
• select into
• 使用writetext/updatetext插入或附加資料
• 重建索引

在這種模式下，會用bitmap image記錄發生最小日誌化的區。如果資料庫故障導致資料檔案不了用，並且日誌尾部包含最小化日誌，不能做日誌尾部備份，因為這個操作需要訪問資料檔案中資料修改後的區。這種模式適用於大容量操作，但是如果事務包含最小化日誌，則不能進行還原時間點，只能還原到之前。

復原模式擴充說明：

如上所說，復原模式是資料庫層級的配置項，在建立過程中及後續使用中均可修改，但是由於種種原因，盡量在規劃階段就做好配置，並且在建立過程中明確指定。

這個選項主要用於決定資料庫是否可以（或者需要）做記錄備份？什麼事務需要被記錄？還有是否可以做其他類型的備份還原作業等。

簡單模式：

某些操作能被最小化日誌，這裡要說明一下，很多人以為簡單模式下“不記錄日誌”，其實這是很嚴重的誤解，會導致後續使用的很多問題，無論任何復原模式，都會記錄日誌，只是記錄的形式和內容不同。在簡單模式下，記錄備份選項被禁用，帶來的影響是不支援還原時間點、頁還原，而檔案還原功能僅限於READONLY檔案組中的次要資料檔案。

簡單模式是最容易管理的復原模式，在這種模式下，可以進行完整Database Backup、差異Database Backup和檔案備份，但是不能進行記錄備份。在記錄備份一文會詳細介紹，但是在這裡要提一下，關於日誌空間重用的問題，不管任何復原模式，都會有一個系統進程在後台運行——CHECKPOINT，每當這個進程啟動時，會把資料庫的記錄檔（通常就是LDF檔案）中，非活動的事務寫入資料檔案，然後把這部分的空間標識為“可重用”，這個步驟稱為日誌截斷，在簡單模式下稱為自動截斷（auto-truncate），記住可重用不代資料表空間被清空，唯一可以清空LDF檔案物理大小的操作是收縮資料庫/檔案操作。簡單模式會自動執行這個截斷操作，截斷後，日誌空間可被新事物重新使用，從宏觀變現來說，就是LDF檔案的物理大小不增加，或者增加緩慢，其實當使用簡單模式，並且LDF合適的情況下，如果LDF物理大小還在增長，可能就需要引起注意。

由於日誌的自動截斷，導致簡單模式下無法進行時間點恢複，也無法進行記錄備份。但是對於對資料要求不高的系統，或者SLA（在還原基礎一文中介紹）沒有什麼特殊要求的環境，可以使用這種模式，可以最大限度減少對日誌的管理。但是不是意味著使用了這種模式，就不用管理日誌了，對於一些大規模、長時間啟動並執行批處理，會引起大量的活動事務，此時LDF檔案依舊會迅速增長，引起一些潛在的問題。對此，儘可能把批處理拆分為多個、短事務。

簡單來說，這種模式的優缺點：

優點：

• 易於管理，大部分情況下不需要

缺點：

• 不能進行交易記錄備份，無法還原時間點
• 資料丟失的風險增大

選擇依據：根據業務需要選擇，對於非常重要的資料庫，無論當前資料庫大小，都不要使用這種模式，詳細內容參考還原基礎中SLA的內容。

完整模式：

完整模式很多概念都是相對於簡單模式來說的，這種模式下，所有操作被完整地記錄在交易記錄檔中，並且不會發生自動截斷（除了資料庫完全沒做過最少一次完整備份），交易記錄只有在交易記錄備份發生時，才會截斷到資料檔案，並且使對應部分可用。這種模式能夠執行所有類型的備份還原選項，特別是可以進行時間點恢複，保證資料接近0丟失。這是幾乎所有正式環境（也稱生產環境）使用的復原模式。

優點：

• 能夠完整記錄資料庫操作
• 進行時間點恢複，保證資料儘可能0丟失

缺點：

• 需要嚴格管理交易記錄檔
• 資料庫規模可能會變得難以控制

大容量記錄模式：

這是用的最少的復原模式，讀者不要給名字忽悠了，見過很多人在進行大容量操作時切換到這種模式，然後操作完再切換回來，這種操作其實比較危險。不建議使用。另外，它支援記錄備份，能進行一定程度的時間點恢複。除了前面提到的可最小化日誌的操作，其日常使用和管理與完整模式無疑。可以理解為是完整模式和簡單模式的過渡。

缺點：

如果資料檔案突然變得不可用，並且日誌尾部包含了大容量記錄模式下進行的最小化日誌操作，那麼不可能進行日誌尾部備份，因為這種備份要求訪問資料修改所發生的區，而這個區在最小化日誌操作中僅記錄“發生了操作”，而沒有完整地記錄操作內容。導致無法進行還原時間點，存在一定的資料丟失風險。做好交易管理的話，其實這種模式基本上沒什麼存在的價值。

備份成份：

現在來說說一個備份會包含什麼內容，很多人以為，特別是完整Database Backup，就是把所有東西都備份，其實他們被名字迷惑了。在介紹備份成份前，先介紹SQL Server的資料庫成份，SQL Server資料庫是一系列基於Windows的檔案，最簡單的模式包含一個資料檔案（預設尾碼名為MDF）和一個記錄檔（預設尾碼名為LDF），尾碼名能改，但是沒有任何理由去改。後果很嚴重…。這兩個檔案在建立資料庫時就自動建立，在後續運行當中，可能會建立多個資料檔案（預設尾碼名為ndf），多個記錄檔（大部分情況下沒必要，在記錄備份一文介紹），還有一些檔案組，每個檔案組包含若干個檔案。

資料檔案：

資料檔案是用於儲存系統及使用者資料及對象，簡單來說，就是資料、表、視圖、預存程序、觸發器等等。除此之外，還包含許可權資訊。每個資料庫最少要有一個資料檔案，預設為主要資料檔案，primary data file，預設尾碼名為.MDF。儲存在主檔案組（primary Filegroup中），如果需要新加檔案，這些檔案就是次要資料檔案（雖然名字為次要，但是一點都不次要…），預設尾碼名為.NDF。

主要資料檔案包含：所有系統對象和資料、預設情況下所有使用者自訂的對象和資料。還有其他次要資料檔案的地址。

檔案組：

檔案組是檔案的一個邏輯集合，它可以包含一個或者多個資料檔案，預設建立資料庫時就會建立一個primary 檔案組，存放primary資料檔案。這個同時是default檔案組，所有資料都會存放到這裡，除非額外指定，default檔案組可以改，前提是有兩個或以上的檔案組，這樣可以把資料強制寫到別的檔案組中，有時候通過這種方式可以緩解磁碟的壓力。另外primary檔案組還存了其他所有檔案組的路徑。

對於多個檔案組的資料庫，可以進行檔案組備份，這種方式對於超大型資料庫（VLDB）非常有效，因為據我工作經驗，即使一個150G的庫做一個完整備份，也往往要進行20分鐘左右，如果是150T的庫，恐怕幾個小時都搞不定，這時候，檔案組備份就起到很重要的作用，把檔案組控制在一定的大小，然後每次備份只對單獨檔案組進行，這樣可以把一個連續的備份操作拆分為很多小操作。另外，檔案組可以設為唯讀（read-only），這樣可以在純讀操作中，減少鎖和等待的產生，對效能方面有一定程度上的協助。對於檔案組配置放在其他章節，這裡不累贅。

需要提醒的是，檔案組帶來效能方面的改進同時，也帶來了管理方面複雜度的提升。所以需要謹慎考慮。

交易記錄：

這部分也有單獨的介紹，這裡只做簡介，所有SQLServer資料庫、所有復原模式下，都有最少一個交易記錄檔。雖然後面有專門的文章介紹，但是這裡要不厭其煩地提醒，別因為任何模式、或者LDF檔案太大就刪除LDF讓SQLServer，最嚴重的情況是會導致你的資料庫無法使用。

 

備份類型：

目前微軟發行的SQLServer版本中，支援以下類型的備份：完整Database Backup、差異Database Backup、交易記錄備份（後稱記錄備份）、檔案和檔案組備份、部份備份，但是如前面所說，根據SQL Server版本不同，有些備份類型不支援，另外根據復原模式的不同，某些備份類型也不支援。資料檔案、檔案組及記錄檔組成了SQL Server資料庫，並且成為了各種備份類型的對象。下面簡介一下各種備份類型：

Database Backup：把主要資料檔案和次要資料檔案（如果有）上面的資料和對象存入備份檔案中，這類細分為：

• 完整Database Backup：備份特定資料庫的所有檔案的所有資料和對象，還有足以用於在故障時恢複資料庫到一致性狀態的日誌部分。
• 差異Database Backup：備份特定資料庫上自最近一次完整Database Backup之後發生修改的所有資料檔案的資料和對象。

交易記錄備份：把特定資料庫自上一次記錄備份後寫入LDF檔案的日誌記錄寫入備份檔案。檔案備份：把資料檔案或者檔案組中的資料及對象寫入備份檔案，可以細分為：

• 完整檔案備份：備份在特定資料檔案或檔案組上的所有資料和對象。
• 差異檔案備份：備份從上一次完整檔案備份後特定資料檔案或檔案組中修改的資料和對象。
• 部份備份（完整部份備份）：備份資料庫中除唯讀檔案/檔案組外（除非特殊指定）的所有可寫部分。
• 差異部份備份：備份自上一次完整部份備份後發生變更的資料和對象。

再次說明，這些備份類型不是總是可用的，有些先決條件，特別是復原模式，本系列將逐步示範這些操作。

備份需要考慮的因素：

備份時需要考慮以下幾個因素，不能認為備份是簡單操作，作為任何資料庫管理（包括專業DBA或者兼職管理員），備份都是第一要務，所以要認真對待：

• SLA
• 備份存放位置
• 備份周期及備份類型組合
• 備份檔案存放周期
• 執行備份的工具
• 對效能的影響

這些部分將在後續陸續介紹。

What’s the next?

1、準備環境，本系列主要使用Windows Server 2012 R2+SQL Server 2008 R2企業版+AdventureWorks 2008 R2資料庫及為了示範而額外建立的一些資料庫。

2、下文將示範完整Database Backup，需要注意，是完整Database Backup，而不是完整備份，雖然大部分情況下這是等價的，但是完整備份實際上包含完整檔案備份，為了減少誤解，這裡需要說明是Database Backup。