SQL Server 備份

前言

為什么要備份？理由很簡(jiǎn)單——為了還原/恢復(fù)。當(dāng)然，如果不備份，還可以通過(guò)磁盤(pán)恢復(fù)來(lái)找回丟失的文件，不過(guò)SQL Server很生氣，后果很?chē)?yán)重。到時(shí)候你就知道為什么先叫你備份一次再開(kāi)始看文章了?！蒧_∩。本系列將介紹SQL Server所有可用的備份還原功能，并盡可能用實(shí)例說(shuō)話。

什么是備份？SQL Server基于Windows，以文件形式存放資料，所以備份就是Windows上SQL Server相關(guān)文件的一個(gè)某個(gè)時(shí)間點(diǎn)的副本。根據(jù)備份類型的不同，副本的種類和內(nèi)容也有不同。

備份類型有哪些？SQL Server 目前版本中，可用的備份類型有：完整數(shù)據(jù)庫(kù)備份、差異數(shù)據(jù)庫(kù)備份、事務(wù)日志備份（后稱日志備份）、文件和文件組備份、部分備份，根據(jù)SQL Server版本不同，有些備份類型不支持，另外根據(jù)恢復(fù)模式的不同，某些備份類型也不支持。

什么是恢復(fù)模式？

很多人只把關(guān)注點(diǎn)放在備份上面，而沒(méi)有在意恢復(fù)模式，其實(shí)所有的備份都應(yīng)該從恢復(fù)模式作為切入點(diǎn)。恢復(fù)模式實(shí)際上是一個(gè)控制備份還原的行為的數(shù)據(jù)庫(kù)級(jí)別選項(xiàng)。SQL Server 在當(dāng)前所有發(fā)布版本中只有三種恢復(fù)模式：簡(jiǎn)單恢復(fù)模式（后面簡(jiǎn)稱簡(jiǎn)單模式），大容量日志恢復(fù)模式（后面簡(jiǎn)稱大容量模式），完整恢復(fù)模式（后稱完整模式）。

本文從恢復(fù)模式開(kāi)始，提醒一下，絕大部分的專業(yè)屬于都會(huì)陸續(xù)解釋，如果讀者有不明白，可以繼續(xù)往下看或者上網(wǎng)搜索：

1.簡(jiǎn)單模式，Simple recovery model：某些操作可以被最小日志化。這種模式下，不支持日志備份、時(shí)間點(diǎn)恢復(fù)和頁(yè)恢復(fù)。且文件恢復(fù)功能僅限于次要數(shù)據(jù)文件中的只讀文件。

2.大容量日志模式，Bulk-logged recovery model：和完整模式類似，有時(shí)候可以理解為完整模式于簡(jiǎn)單模式的過(guò)渡模式。這種模式對(duì)某些大容量操作進(jìn)行最小日志化，支持完整備份中的備份還原策略，但是由于某些操作被最小日志化，所以不能保證時(shí)間點(diǎn)恢復(fù)。

3.完整模式，F(xiàn)ull recovery model： 在這個(gè)模式下，所以操作都被完整記錄下來(lái)，并且支持所有類型的備份還原策略。

默認(rèn)情況下，新庫(kù)會(huì)繼承Model庫(kù)的配置，包括恢復(fù)模式，也就是FULL模式。可以在創(chuàng)建或日常使用過(guò)程中修改，并且不需要重啟服務(wù)。恢復(fù)模式最重要的區(qū)別在于對(duì)待日志的行為。

簡(jiǎn)單模式：

是三種模式中最容易管理的，可以進(jìn)行完整，差異和文件備份，但是不能做日志備份。在這種模式下，每當(dāng)Checkpoint 進(jìn)程發(fā)生時(shí)，會(huì)自動(dòng)把日志文件中不活動(dòng)的日志（在日志備份一文介紹）寫(xiě)入數(shù)據(jù)文件，寫(xiě)入后，對(duì)應(yīng)的日志文件中的空間就可供新事務(wù)使用，注意這種空間重用或者截?cái)嗖⒉蛔詣?dòng)減少日志文件的物理大小，如果需要減少空間，需要使用DBCC SHRINKFILE/DATABASE等命令實(shí)現(xiàn)。讓日志空間重用的過(guò)程叫做截?cái)?。在?jiǎn)單模式下這個(gè)過(guò)程稱為自動(dòng)截?cái)?auto-truncate)。在這種模式下，日志通常不需要管理，但是對(duì)于單個(gè)的大事務(wù)，日志文件可能會(huì)增長(zhǎng)得很快，這種情況下最好把批處理降為小的批。簡(jiǎn)單模式最主要的限制是不能進(jìn)行日志備份，也就是說(shuō)無(wú)法進(jìn)行時(shí)間點(diǎn)還原。在一些測(cè)試，開(kāi)發(fā)或者SLA要求不嚴(yán)格的環(huán)境下，可以使用這種模式。

完整模式：

這種模式下，所有數(shù)據(jù)庫(kù)操作都被完整地記錄在日志中，2008出現(xiàn)某些操作在這種模式下也還是最小化日志。并且不是自動(dòng)截?cái)?。它支持任何備份還原策略，特別是時(shí)間點(diǎn)還原，在日志還原一章介紹。即使發(fā)生Checkpoint ，不活動(dòng)的事務(wù)也不會(huì)截?cái)嗟綌?shù)據(jù)文件中。唯一能控制日志文件的只有日志備份，所以這種模式下日志備份極其重要，一方面提供時(shí)間點(diǎn)還原，另外一方面控制日志文件大小。

日志文件會(huì)完整保存自上一次日志備份后的事務(wù)。使用copy_only或者no_truncate選項(xiàng)均不會(huì)截?cái)嗳罩尽?/p>

大容量日志：

這種模式是最少用到的，某些操作會(huì)被最小日志化，包含：

• 使用bcp進(jìn)行導(dǎo)入
• bulk
• insertinsert
• select *from openrowset(bulk )
• select into
• 使用writetext/updatetext插入或附加數(shù)據(jù)
• 重建索引

在這種模式下，會(huì)用bitmap image記錄發(fā)生最小日志化的區(qū)。如果數(shù)據(jù)庫(kù)故障導(dǎo)致數(shù)據(jù)文件不了用，并且日志尾部包含最小化日志，不能做日志尾部備份，因?yàn)檫@個(gè)操作需要訪問(wèn)數(shù)據(jù)文件中數(shù)據(jù)修改后的區(qū)。這種模式適用于大容量操作，但是如果事務(wù)包含最小化日志，則不能進(jìn)行時(shí)間點(diǎn)還原，只能還原到之前。

恢復(fù)模式擴(kuò)展說(shuō)明：

如上所說(shuō)，恢復(fù)模式是數(shù)據(jù)庫(kù)級(jí)別的配置項(xiàng)，在創(chuàng)建過(guò)程中及后續(xù)使用中均可修改，但是由于種種原因，盡量在規(guī)劃階段就做好配置，并且在創(chuàng)建過(guò)程中明確指定。

這個(gè)選項(xiàng)主要用于決定數(shù)據(jù)庫(kù)是否可以（或者需要）做日志備份？什么事務(wù)需要被記錄？還有是否可以做其他類型的備份還原操作等。

簡(jiǎn)單模式：

某些操作能被最小化日志，這里要說(shuō)明一下，很多人以為簡(jiǎn)單模式下“不記錄日志”，其實(shí)這是很?chē)?yán)重的誤解，會(huì)導(dǎo)致后續(xù)使用的很多問(wèn)題，無(wú)論任何恢復(fù)模式，都會(huì)記錄日志，只是記錄的形式和內(nèi)容不同。在簡(jiǎn)單模式下，日志備份選項(xiàng)被禁用，帶來(lái)的影響是不支持時(shí)間點(diǎn)還原、頁(yè)還原，而文件還原功能僅限于READONLY文件組中的次要數(shù)據(jù)文件。

簡(jiǎn)單模式是最容易管理的恢復(fù)模式，在這種模式下，可以進(jìn)行完整數(shù)據(jù)庫(kù)備份、差異數(shù)據(jù)庫(kù)備份和文件備份，但是不能進(jìn)行日志備份。在日志備份一文會(huì)詳細(xì)介紹，但是在這里要提一下，關(guān)于日志空間重用的問(wèn)題，不管任何恢復(fù)模式，都會(huì)有一個(gè)系統(tǒng)進(jìn)程在后臺(tái)運(yùn)行——CHECKPOINT，每當(dāng)這個(gè)進(jìn)程啟動(dòng)時(shí)，會(huì)把數(shù)據(jù)庫(kù)的日志文件（通常就是LDF文件）中，非活動(dòng)的事務(wù)寫(xiě)入數(shù)據(jù)文件，然后把這部分的空間標(biāo)識(shí)為“可重用”，這個(gè)步驟稱為日志截?cái)?，在?jiǎn)單模式下稱為自動(dòng)截?cái)啵╝uto-truncate），記住可重用不代表空間被清空，唯一可以清空LDF文件物理大小的操作是收縮數(shù)據(jù)庫(kù)/文件操作。簡(jiǎn)單模式會(huì)自動(dòng)執(zhí)行這個(gè)截?cái)嗖僮鳎財(cái)嗪?，日志空間可被新事物重新使用，從宏觀變現(xiàn)來(lái)說(shuō)，就是LDF文件的物理大小不增加，或者增加緩慢，其實(shí)當(dāng)使用簡(jiǎn)單模式，并且LDF合適的情況下，如果LDF物理大小還在增長(zhǎng)，可能就需要引起注意。

由于日志的自動(dòng)截?cái)啵瑢?dǎo)致簡(jiǎn)單模式下無(wú)法進(jìn)行時(shí)間點(diǎn)恢復(fù)，也無(wú)法進(jìn)行日志備份。但是對(duì)于對(duì)數(shù)據(jù)要求不高的系統(tǒng)，或者SLA（在還原基礎(chǔ)一文中介紹）沒(méi)有什么特殊要求的環(huán)境，可以使用這種模式，可以最大限度減少對(duì)日志的管理。但是不是意味著使用了這種模式，就不用管理日志了，對(duì)于一些大規(guī)模、長(zhǎng)時(shí)間運(yùn)行的批處理，會(huì)引起大量的活動(dòng)事務(wù)，此時(shí)LDF文件依舊會(huì)迅速增長(zhǎng)，引起一些潛在的問(wèn)題。對(duì)此，盡可能把批處理拆分為多個(gè)、短事務(wù)。

簡(jiǎn)單來(lái)說(shuō)，這種模式的優(yōu)缺點(diǎn)：

優(yōu)點(diǎn)：

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

缺點(diǎn)：

• 不能進(jìn)行事務(wù)日志備份，無(wú)法時(shí)間點(diǎn)還原
• 數(shù)據(jù)丟失的風(fēng)險(xiǎn)增大

選擇依據(jù)：根據(jù)業(yè)務(wù)需要選擇，對(duì)于非常重要的數(shù)據(jù)庫(kù)，無(wú)論當(dāng)前數(shù)據(jù)庫(kù)大小，都不要使用這種模式，詳細(xì)內(nèi)容參考還原基礎(chǔ)中SLA的內(nèi)容。

完整模式：

完整模式很多概念都是相對(duì)于簡(jiǎn)單模式來(lái)說(shuō)的，這種模式下，所有操作被完整地記錄在事務(wù)日志文件中，并且不會(huì)發(fā)生自動(dòng)截?cái)啵ǔ藬?shù)據(jù)庫(kù)完全沒(méi)做過(guò)最少一次完整備份），事務(wù)日志只有在事務(wù)日志備份發(fā)生時(shí)，才會(huì)截?cái)嗟綌?shù)據(jù)文件，并且使對(duì)應(yīng)部分可用。這種模式能夠執(zhí)行所有類型的備份還原選項(xiàng)，特別是可以進(jìn)行時(shí)間點(diǎn)恢復(fù)，保證數(shù)據(jù)接近0丟失。這是幾乎所有正式環(huán)境（也稱生產(chǎn)環(huán)境）使用的恢復(fù)模式。

優(yōu)點(diǎn)：

• 能夠完整記錄數(shù)據(jù)庫(kù)操作
• 進(jìn)行時(shí)間點(diǎn)恢復(fù)，保證數(shù)據(jù)盡可能0丟失

缺點(diǎn)：

• 需要嚴(yán)格管理事務(wù)日志文件
• 數(shù)據(jù)庫(kù)規(guī)?？赡軙?huì)變得難以控制

大容量日志模式：

這是用的最少的恢復(fù)模式，讀者不要給名字忽悠了，見(jiàn)過(guò)很多人在進(jìn)行大容量操作時(shí)切換到這種模式，然后操作完再切換回來(lái)，這種操作其實(shí)比較危險(xiǎn)。不建議使用。另外，它支持日志備份，能進(jìn)行一定程度的時(shí)間點(diǎn)恢復(fù)。除了前面提到的可最小化日志的操作，其日常使用和管理與完整模式無(wú)疑?？梢岳斫鉃槭峭暾Ｊ胶秃?jiǎn)單模式的過(guò)渡。

缺點(diǎn)：

如果數(shù)據(jù)文件突然變得不可用，并且日志尾部包含了大容量日志模式下進(jìn)行的最小化日志操作，那么不可能進(jìn)行日志尾部備份，因?yàn)檫@種備份要求訪問(wèn)數(shù)據(jù)修改所發(fā)生的區(qū)，而這個(gè)區(qū)在最小化日志操作中僅記錄“發(fā)生了操作”，而沒(méi)有完整地記錄操作內(nèi)容。導(dǎo)致無(wú)法進(jìn)行時(shí)間點(diǎn)還原，存在一定的數(shù)據(jù)丟失風(fēng)險(xiǎn)。做好事務(wù)管理的話，其實(shí)這種模式基本上沒(méi)什么存在的價(jià)值。

備份成份：

現(xiàn)在來(lái)說(shuō)說(shuō)一個(gè)備份會(huì)包含什么內(nèi)容，很多人以為，特別是完整數(shù)據(jù)庫(kù)備份，就是把所有東西都備份，其實(shí)他們被名字迷惑了。在介紹備份成份前，先介紹SQL Server的數(shù)據(jù)庫(kù)成份，SQL Server數(shù)據(jù)庫(kù)是一系列基于Windows的文件，最簡(jiǎn)單的模式包含一個(gè)數(shù)據(jù)文件（默認(rèn)后綴名為MDF）和一個(gè)日志文件（默認(rèn)后綴名為L(zhǎng)DF），后綴名能改，但是沒(méi)有任何理由去改。后果很?chē)?yán)重…。這兩個(gè)文件在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)就自動(dòng)創(chuàng)建，在后續(xù)運(yùn)行當(dāng)中，可能會(huì)創(chuàng)建多個(gè)數(shù)據(jù)文件（默認(rèn)后綴名為ndf），多個(gè)日志文件（大部分情況下沒(méi)必要，在日志備份一文介紹），還有一些文件組，每個(gè)文件組包含若干個(gè)文件。

數(shù)據(jù)文件：

數(shù)據(jù)文件是用于存儲(chǔ)系統(tǒng)及用戶數(shù)據(jù)及對(duì)象，簡(jiǎn)單來(lái)說(shuō)，就是數(shù)據(jù)、表、視圖、存儲(chǔ)過(guò)程、觸發(fā)器等等。除此之外，還包含權(quán)限信息。每個(gè)數(shù)據(jù)庫(kù)最少要有一個(gè)數(shù)據(jù)文件，默認(rèn)為主數(shù)據(jù)文件，primary data file，默認(rèn)后綴名為.MDF。存儲(chǔ)在主文件組（primary Filegroup中），如果需要新加文件，這些文件就是次要數(shù)據(jù)文件（雖然名字為次要，但是一點(diǎn)都不次要…），默認(rèn)后綴名為.NDF。

主數(shù)據(jù)文件包含：所有系統(tǒng)對(duì)象和數(shù)據(jù)、默認(rèn)情況下所有用戶自定義的對(duì)象和數(shù)據(jù)。還有其他次要數(shù)據(jù)文件的地址。

文件組：

文件組是文件的一個(gè)邏輯集合，它可以包含一個(gè)或者多個(gè)數(shù)據(jù)文件，默認(rèn)創(chuàng)建數(shù)據(jù)庫(kù)時(shí)就會(huì)創(chuàng)建一個(gè)primary 文件組，存放primary數(shù)據(jù)文件。這個(gè)同時(shí)是default文件組，所有數(shù)據(jù)都會(huì)存放到這里，除非額外指定，default文件組可以改，前提是有兩個(gè)或以上的文件組，這樣可以把數(shù)據(jù)強(qiáng)制寫(xiě)到別的文件組中，有時(shí)候通過(guò)這種方式可以緩解磁盤(pán)的壓力。另外primary文件組還存了其他所有文件組的路徑。

對(duì)于多個(gè)文件組的數(shù)據(jù)庫(kù)，可以進(jìn)行文件組備份，這種方式對(duì)于超大型數(shù)據(jù)庫(kù)（VLDB）非常有效，因?yàn)閾?jù)我工作經(jīng)驗(yàn)，即使一個(gè)150G的庫(kù)做一個(gè)完整備份，也往往要進(jìn)行20分鐘左右，如果是150T的庫(kù)，恐怕幾個(gè)小時(shí)都搞不定，這時(shí)候，文件組備份就起到很重要的作用，把文件組控制在一定的大小，然后每次備份只對(duì)單獨(dú)文件組進(jìn)行，這樣可以把一個(gè)連續(xù)的備份操作拆分為很多小操作。另外，文件組可以設(shè)為只讀（read-only），這樣可以在純讀操作中，減少鎖和等待的產(chǎn)生，對(duì)性能方面有一定程度上的幫助。對(duì)于文件組配置放在其他章節(jié)，這里不累贅。

需要提醒的是，文件組帶來(lái)性能方面的改進(jìn)同時(shí)，也帶來(lái)了管理方面復(fù)雜度的提升。所以需要慎重考慮。

事務(wù)日志：

這部分也有單獨(dú)的介紹，這里只做簡(jiǎn)介，所有SQLServer數(shù)據(jù)庫(kù)、所有恢復(fù)模式下，都有最少一個(gè)事務(wù)日志文件。雖然后面有專門(mén)的文章介紹，但是這里要不厭其煩地提醒，別因?yàn)槿魏文Ｊ健⒒蛘週DF文件太大就刪除LDF讓SQLServer，最嚴(yán)重的情況是會(huì)導(dǎo)致你的數(shù)據(jù)庫(kù)無(wú)法使用。

備份類型：

目前微軟已發(fā)布的SQLServer版本中，支持以下類型的備份：完整數(shù)據(jù)庫(kù)備份、差異數(shù)據(jù)庫(kù)備份、事務(wù)日志備份（后稱日志備份）、文件和文件組備份、部分備份，但是如前面所說(shuō)，根據(jù)SQL Server版本不同，有些備份類型不支持，另外根據(jù)恢復(fù)模式的不同，某些備份類型也不支持。數(shù)據(jù)文件、文件組及日志文件組成了SQL Server數(shù)據(jù)庫(kù)，并且成為了各種備份類型的對(duì)象。下面簡(jiǎn)介一下各種備份類型：

數(shù)據(jù)庫(kù)備份：把主數(shù)據(jù)文件和次要數(shù)據(jù)文件（如果有）上面的數(shù)據(jù)和對(duì)象存入備份文件中，這類細(xì)分為：

• 完整數(shù)據(jù)庫(kù)備份：備份特定數(shù)據(jù)庫(kù)的所有文件的所有數(shù)據(jù)和對(duì)象，還有足以用于在故障時(shí)恢復(fù)數(shù)據(jù)庫(kù)到一致性狀態(tài)的日志部分。
• 差異數(shù)據(jù)庫(kù)備份：備份特定數(shù)據(jù)庫(kù)上自最近一次完整數(shù)據(jù)庫(kù)備份之后發(fā)生修改的所有數(shù)據(jù)文件的數(shù)據(jù)和對(duì)象。事務(wù)日志備份：把特定數(shù)據(jù)庫(kù)自上一次日志備份后寫(xiě)入LDF文件的日志記錄寫(xiě)入備份文件。

文件備份：把數(shù)據(jù)文件或者文件組中的數(shù)據(jù)及對(duì)象寫(xiě)入備份文件，可以細(xì)分為：

• 完整文件備份：備份在特定數(shù)據(jù)文件或文件組上的所有數(shù)據(jù)和對(duì)象。
• 差異文件備份：備份從上一次完整文件備份后特定數(shù)據(jù)文件或文件組中修改的數(shù)據(jù)和對(duì)象。
• 部分備份（完整部分備份）：備份數(shù)據(jù)庫(kù)中除只讀文件/文件組外（除非特殊指定）的所有可寫(xiě)部分。
• 差異部分備份：備份自上一次完整部分備份后發(fā)生變更的數(shù)據(jù)和對(duì)象。

再次說(shuō)明，這些備份類型不是總是可用的，有些先決條件，特別是恢復(fù)模式，本系列將逐步演示這些操作。

備份需要考慮的因素：

備份時(shí)需要考慮以下幾個(gè)因素，不能認(rèn)為備份是簡(jiǎn)單操作，作為任何數(shù)據(jù)庫(kù)管理（包括專業(yè)DBA或者兼職管理人員），備份都是第一要?jiǎng)?wù)，所以要認(rèn)真對(duì)待：

• SLA
• 備份存放位置
• 備份周期及備份類型組合
• 備份文件存放周期
• 執(zhí)行備份的工具
• 對(duì)性能的影響

這些部分將在后續(xù)陸續(xù)介紹。

What's the next?

1、準(zhǔn)備環(huán)境，本系列主要使用Windows Server 2012 R2+SQL Server 2008 R2企業(yè)版+AdventureWorks 2008 R2數(shù)據(jù)庫(kù)及為了演示而額外創(chuàng)建的一些數(shù)據(jù)庫(kù)。

2、下文將演示完整數(shù)據(jù)庫(kù)備份，需要注意，是完整數(shù)據(jù)庫(kù)備份，而不是完整備份，雖然大部分情況下這是等價(jià)的，但是完整備份實(shí)際上包含完整文件備份，為了減少誤解，這里需要說(shuō)明是數(shù)據(jù)庫(kù)備份。

感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！