SQL Server 備份
前言
為什麼要備份?理由很簡單——為了還原/恢復。當然,如果不備份,還可以通過磁盤恢復來找回丟失的文件,不過SQL Server很生氣,後果很嚴重。到時候你就知道為什麼先叫你備份一次再開始看文章了。∩__∩。本系列將介紹SQL Server所有可用的備份還原功能,並盡可能用實例說話。
什麼是備份?SQL Server基於Windows,以文件形式存放資料,所以備份就是Windows上SQL Server相關文件的一個某個時間點的副本。根據備份類型的不同,副本的種類和內容也有不同。
備份類型有哪些?SQL Server 目前版本中,可用的備份類型有:完整數據庫備份、差異數據庫備份、事務日志備份(後稱日志備份)、文件和文件組備份、部分備份,根據SQL Server版本不同,有些備份類型不支持,另外根據恢復模式的不同,某些備份類型也不支持。
什麼是恢復模式?
很多人只把關注點放在備份上面,而沒有在意恢復模式,其實所有的備份都應該從恢復模式作為切入點。恢復模式實際上是一個控制備份還原的行為的數據庫級別選項。SQL Server 在當前所有發布版本中只有三種恢復模式:簡單恢復模式(後面簡稱簡單模式),大容量日志恢復模式(後面簡稱大容量模式),完整恢復模式(後稱完整模式)。
本文從恢復模式開始,提醒一下,絕大部分的專業屬於都會陸續解釋,如果讀者有不明白,可以繼續往下看或者上網搜索:
1.簡單模式,Simple recovery model:某些操作可以被最小日志化。這種模式下,不支持日志備份、時間點恢復和頁恢復。且文件恢復功能僅限於次要數據文件中的只讀文件。
2.大容量日志模式,Bulk-logged recovery model:和完整模式類似,有時候可以理解為完整模式於簡單模式的過渡模式。這種模式對某些大容量操作進行最小日志化,支持完整備份中的備份還原策略,但是由於某些操作被最小日志化,所以不能保證時間點恢復。
3.完整模式,Full recovery model: 在這個模式下,所以操作都被完整記錄下來,並且支持所有類型的備份還原策略。
默認情況下,新庫會繼承Model庫的配置,包括恢復模式,也就是FULL模式。可以在創建或日常使用過程中修改,並且不需要重啟服務。恢復模式最重要的區別在於對待日志的行為。
簡單模式:
是三種模式中最容易管理的,可以進行完整,差異和文件備份,但是不能做日志備份。在這種模式下,每當Checkpoint 進程發生時,會自動把日志文件中不活動的日志(在日志備份一文介紹)寫入數據文件,寫入後,對應的日志文件中的空間就可供新事務使用,注意這種空間重用或者截斷並不自動減少日志文件的物理大小,如果需要減少空間,需要使用DBCC SHRINKFILE/DATABASE等命令實現。讓日志空間重用的過程叫做截斷。在簡單模式下這個過程稱為自動截斷(auto-truncate)。在這種模式下,日志通常不需要管理,但是對於單個的大事務,日志文件可能會增長得很快,這種情況下最好把批處理降為小的批。簡單模式最主要的限制是不能進行日志備份,也就是說無法進行時間點還原。在一些測試,開發或者SLA要求不嚴格的環境下,可以使用這種模式。
完整模式:
這種模式下,所有數據庫操作都被完整地記錄在日志中,2008出現某些操作在這種模式下也還是最小化日志。並且不是自動截斷。它支持任何備份還原策略,特別是時間點還原,在日志還原一章介紹。即使發生Checkpoint ,不活動的事務也不會截斷到數據文件中。唯一能控制日志文件的只有日志備份,所以這種模式下日志備份極其重要,一方面提供時間點還原,另外一方面控制日志文件大小。
日志文件會完整保存自上一次日志備份後的事務。使用copy_only或者no_truncate選項均不會截斷日志。
大容量日志:
這種模式是最少用到的,某些操作會被最小日志化,包含:
在這種模式下,會用bitmap image記錄發生最小日志化的區。如果數據庫故障導致數據文件不了用,並且日志尾部包含最小化日志,不能做日志尾部備份,因為這個操作需要訪問數據文件中數據修改後的區。這種模式適用於大容量操作,但是如果事務包含最小化日志,則不能進行時間點還原,只能還原到之前。
恢復模式擴展說明:
如上所說,恢復模式是數據庫級別的配置項,在創建過程中及後續使用中均可修改,但是由於種種原因,盡量在規劃階段就做好配置,並且在創建過程中明確指定。
這個選項主要用於決定數據庫是否可以(或者需要)做日志備份?什麼事務需要被記錄?還有是否可以做其他類型的備份還原操作等。
簡單模式:
某些操作能被最小化日志,這裡要說明一下,很多人以為簡單模式下“不記錄日志”,其實這是很嚴重的誤解,會導致後續使用的很多問題,無論任何恢復模式,都會記錄日志,只是記錄的形式和內容不同。在簡單模式下,日志備份選項被禁用,帶來的影響是不支持時間點還原、頁還原,而文件還原功能僅限於READONLY文件組中的次要數據文件。
簡單模式是最容易管理的恢復模式,在這種模式下,可以進行完整數據庫備份、差異數據庫備份和文件備份,但是不能進行日志備份。在日志備份一文會詳細介紹,但是在這裡要提一下,關於日志空間重用的問題,不管任何恢復模式,都會有一個系統進程在後台運行——CHECKPOINT,每當這個進程啟動時,會把數據庫的日志文件(通常就是LDF文件)中,非活動的事務寫入數據文件,然後把這部分的空間標識為“可重用”,這個步驟稱為日志截斷,在簡單模式下稱為自動截斷(auto-truncate),記住可重用不代表空間被清空,唯一可以清空LDF文件物理大小的操作是收縮數據庫/文件操作。簡單模式會自動執行這個截斷操作,截斷後,日志空間可被新事物重新使用,從宏觀變現來說,就是LDF文件的物理大小不增加,或者增加緩慢,其實當使用簡單模式,並且LDF合適的情況下,如果LDF物理大小還在增長,可能就需要引起注意。
由於日志的自動截斷,導致簡單模式下無法進行時間點恢復,也無法進行日志備份。但是對於對數據要求不高的系統,或者SLA(在還原基礎一文中介紹)沒有什麼特殊要求的環境,可以使用這種模式,可以最大限度減少對日志的管理。但是不是意味著使用了這種模式,就不用管理日志了,對於一些大規模、長時間運行的批處理,會引起大量的活動事務,此時LDF文件依舊會迅速增長,引起一些潛在的問題。對此,盡可能把批處理拆分為多個、短事務。
簡單來說,這種模式的優缺點:
優點:
易於管理,大部分情況下不需要缺點:
選擇依據:根據業務需要選擇,對於非常重要的數據庫,無論當前數據庫大小,都不要使用這種模式,詳細內容參考還原基礎中SLA的內容。
完整模式:
完整模式很多概念都是相對於簡單模式來說的,這種模式下,所有操作被完整地記錄在事務日志文件中,並且不會發生自動截斷(除了數據庫完全沒做過最少一次完整備份),事務日志只有在事務日志備份發生時,才會截斷到數據文件,並且使對應部分可用。這種模式能夠執行所有類型的備份還原選項,特別是可以進行時間點恢復,保證數據接近0丟失。這是幾乎所有正式環境(也稱生產環境)使用的恢復模式。
優點:
缺點:
大容量日志模式:
這是用的最少的恢復模式,讀者不要給名字忽悠了,見過很多人在進行大容量操作時切換到這種模式,然後操作完再切換回來,這種操作其實比較危險。不建議使用。另外,它支持日志備份,能進行一定程度的時間點恢復。除了前面提到的可最小化日志的操作,其日常使用和管理與完整模式無疑。可以理解為是完整模式和簡單模式的過渡。
缺點:
如果數據文件突然變得不可用,並且日志尾部包含了大容量日志模式下進行的最小化日志操作,那麼不可能進行日志尾部備份,因為這種備份要求訪問數據修改所發生的區,而這個區在最小化日志操作中僅記錄“發生了操作”,而沒有完整地記錄操作內容。導致無法進行時間點還原,存在一定的數據丟失風險。做好事務管理的話,其實這種模式基本上沒什麼存在的價值。
備份成份:
現在來說說一個備份會包含什麼內容,很多人以為,特別是完整數據庫備份,就是把所有東西都備份,其實他們被名字迷惑了。在介紹備份成份前,先介紹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版本中,支持以下類型的備份:完整數據庫備份、差異數據庫備份、事務日志備份(後稱日志備份)、文件和文件組備份、部分備份,但是如前面所說,根據SQL Server版本不同,有些備份類型不支持,另外根據恢復模式的不同,某些備份類型也不支持。數據文件、文件組及日志文件組成了SQL Server數據庫,並且成為了各種備份類型的對象。下面簡介一下各種備份類型:
數據庫備份:把主數據文件和次要數據文件(如果有)上面的數據和對象存入備份文件中,這類細分為:
文件備份:把數據文件或者文件組中的數據及對象寫入備份文件,可以細分為:
再次說明,這些備份類型不是總是可用的,有些先決條件,特別是恢復模式,本系列將逐步演示這些操作。
備份需要考慮的因素:
備份時需要考慮以下幾個因素,不能認為備份是簡單操作,作為任何數據庫管理(包括專業DBA或者兼職管理人員),備份都是第一要務,所以要認真對待:
這些部分將在後續陸續介紹。
What's the next?
1、准備環境,本系列主要使用Windows Server 2012 R2+SQL Server 2008 R2企業版+AdventureWorks 2008 R2數據庫及為了演示而額外創建的一些數據庫。
2、下文將演示完整數據庫備份,需要注意,是完整數據庫備份,而不是完整備份,雖然大部分情況下這是等價的,但是完整備份實際上包含完整文件備份,為了減少誤解,這裡需要說明是數據庫備份。
感謝閱讀,希望能幫助到大家,謝謝大家對本站的支持!