第8章備份與恢復(fù)

第8章

備份與恢復(fù)8.1備份和恢復(fù)概述8.2備份操作和備份命令8.3恢復(fù)操作和恢復(fù)命令8.4附加數(shù)據(jù)庫(kù)8.1備份和恢復(fù)概述數(shù)據(jù)庫(kù)的備份和恢復(fù)是數(shù)據(jù)庫(kù)管理員維護(hù)數(shù)據(jù)庫(kù)安全性和完整性必不可少的操作，合理地進(jìn)行備份和恢復(fù)可以將可預(yù)見(jiàn)的和不可預(yù)見(jiàn)的問(wèn)題對(duì)數(shù)據(jù)庫(kù)造成的傷害降到最低。當(dāng)運(yùn)行

SQL

Server的服務(wù)器出現(xiàn)故障，或者數(shù)據(jù)庫(kù)遭到某種程度的破壞時(shí)，可以利用以前對(duì)數(shù)據(jù)庫(kù)所做的備份重建或恢復(fù)數(shù)據(jù)庫(kù)。

SQLServer2008數(shù)據(jù)庫(kù)恢復(fù)模式分為三種：完整恢復(fù)模式、大容量日志恢復(fù)模式、簡(jiǎn)單恢復(fù)模式?！敉暾謴?fù)模式。為默認(rèn)恢復(fù)模式。它會(huì)完整記錄下操作數(shù)據(jù)庫(kù)的每一個(gè)步驟。使用完整恢復(fù)模式可以將整個(gè)數(shù)據(jù)庫(kù)恢復(fù)到一個(gè)特定的時(shí)間點(diǎn)，這個(gè)時(shí)間點(diǎn)可以是最近一次可用的備份、一個(gè)特定的日期和時(shí)間或標(biāo)記的事務(wù)。

◆大容量日志恢復(fù)模式。它是對(duì)完整恢復(fù)模式的補(bǔ)充。簡(jiǎn)單地說(shuō)就是要對(duì)大容量操作進(jìn)行最小日志記錄，節(jié)省日志文件的空間（如導(dǎo)入數(shù)據(jù)、批量更新、SELECTINTO等操作時(shí)）。比如一次在數(shù)據(jù)庫(kù)中插入數(shù)十萬(wàn)條記錄時(shí)，在完整恢復(fù)模式下每一個(gè)插入記錄的動(dòng)作都會(huì)記錄在日志中，使日志文件變得非常大，在大容量日志恢復(fù)模式下，只記錄必要的操作，不記錄所有日志，這樣一來(lái)，可以大大提高數(shù)據(jù)庫(kù)的性能，但是由于日志不完整，一旦出現(xiàn)問(wèn)題，數(shù)據(jù)將可能無(wú)法恢復(fù)。因此，一般只有在需要進(jìn)行大量數(shù)據(jù)操作時(shí)才將恢復(fù)模式改為大容量日志恢復(fù)模式，數(shù)據(jù)處理完畢之后，馬上將恢復(fù)模式改回完整恢復(fù)模式。

由于大容量日志恢復(fù)模式不支持時(shí)點(diǎn)恢復(fù)，因此必須在增大日志備份與增加工作丟失風(fēng)險(xiǎn)之間進(jìn)行權(quán)衡。

◆簡(jiǎn)單恢復(fù)模式。在該模式下，數(shù)據(jù)庫(kù)會(huì)自動(dòng)把不活動(dòng)的日志刪除，因此簡(jiǎn)化了備份的還原，但因?yàn)闆](méi)有事務(wù)日志備份，所以不能恢復(fù)到失敗的時(shí)間點(diǎn)。通常，此模式只用于對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)安全要求不太高的數(shù)據(jù)庫(kù)。并且在該模式下，數(shù)據(jù)庫(kù)只能做完整和差異備份。

了解了以上數(shù)據(jù)庫(kù)還原模式后，一般就應(yīng)該知道將自己的數(shù)據(jù)庫(kù)設(shè)置為何種模式了。SQLServer2008提供了四種備份方式：完整備份、差異備份、事務(wù)日志備份、文件和文件組備份。

◆完整備份：備份整個(gè)數(shù)據(jù)庫(kù)的所有內(nèi)容，包括事務(wù)日志。該備份類(lèi)型需要比較大的存儲(chǔ)空間來(lái)存儲(chǔ)備份文件，備份時(shí)間也比較長(zhǎng)，在還原數(shù)據(jù)時(shí)，也只要還原一個(gè)備份文件。

完整備份在所有模式下都適用。

◆差異備份：差異備份是完整備份的補(bǔ)充，只備份上次完整備份后更改的數(shù)據(jù)。相對(duì)于完整備份分來(lái)說(shuō)，差異備份的數(shù)據(jù)量比完整數(shù)據(jù)備份小，備份的速度也比完整備份要快。因此，差異備份通常作為常用的備份方式。在還原數(shù)據(jù)時(shí)，要先還原前一次做的完整備份，然后還原最后一次所做的差異備份，這樣才能讓數(shù)據(jù)庫(kù)里的數(shù)據(jù)恢復(fù)到與最后一次差異備份時(shí)的內(nèi)容相同。

差異備份也適用于所有恢復(fù)模式。8.1備份和恢復(fù)概述◆事務(wù)日志備份：事務(wù)日志備份只備份事務(wù)日志里的內(nèi)容。事務(wù)日志記錄了上一次完整備份或事務(wù)日志備份后數(shù)據(jù)庫(kù)的所有變動(dòng)過(guò)程。事務(wù)日志記錄的是某一段時(shí)間內(nèi)的數(shù)據(jù)庫(kù)變動(dòng)情況，因此在進(jìn)行事務(wù)日志備份之前，必須要進(jìn)行完整備份。與差異備份類(lèi)似，事務(wù)日志備份生成的文件較小、占用時(shí)間較短，但是在還原數(shù)據(jù)時(shí)，除了先要還原完整備份之外，還要依次還原每個(gè)事務(wù)日志備份，而不是只還原最后一個(gè)事務(wù)日志備份（這是與差異備份的區(qū)別）。

事務(wù)日志備份僅適用于完整恢復(fù)模式和大容量日志恢復(fù)模式，不適用于簡(jiǎn)單模式。

◆文件和文件組備份。如果在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，為數(shù)據(jù)庫(kù)創(chuàng)建了多個(gè)數(shù)據(jù)庫(kù)文件或文件組，可以使用該備份方式。使用文件和文件組備份方式可以只備份數(shù)據(jù)庫(kù)中的某些文件，該備份方式在數(shù)據(jù)庫(kù)文件非常龐大時(shí)十分有效，由于每次只備份一個(gè)或幾個(gè)文件或文件組，可以分多次來(lái)備份數(shù)據(jù)庫(kù)，避免大型數(shù)據(jù)庫(kù)備份的時(shí)間過(guò)長(zhǎng)。另外，由于文件和文件組備份只備份其中一個(gè)或多個(gè)數(shù)據(jù)文件，當(dāng)數(shù)據(jù)庫(kù)里的某個(gè)或某些文件損壞時(shí)，可以只還原損壞的文件或文件組備份。

8.1備份和恢復(fù)概述了解了以上數(shù)據(jù)庫(kù)備份方式后，便可以針對(duì)自己的數(shù)據(jù)庫(kù)利用以上方式來(lái)備份數(shù)據(jù)庫(kù)了。合理備份數(shù)據(jù)庫(kù)需要考慮幾方面，首先是數(shù)據(jù)安全，其次是備份文件大小，最后是做備份和還原能承受的時(shí)間范圍。

例如，如果數(shù)據(jù)庫(kù)里每天變動(dòng)的數(shù)據(jù)量很小，可以每周（周日）做一次完整備份，以后的每天（下班前）做一次事務(wù)日志備份，那么一旦數(shù)據(jù)庫(kù)發(fā)生問(wèn)題，可以將數(shù)據(jù)恢復(fù)到前一天（下班時(shí)）的狀態(tài)。

當(dāng)然，也可以在周日時(shí)做一次完整備份，周一到周六每天下班前做一次差異備份，這樣一旦數(shù)據(jù)庫(kù)發(fā)生問(wèn)題，同樣可以將數(shù)據(jù)恢復(fù)到前一天下班時(shí)的狀態(tài)。只是一周的后幾天做差異備份時(shí)，備份的時(shí)間和備份的文件都會(huì)跟著增加。但這也有一個(gè)好處，在數(shù)據(jù)損壞時(shí)，只要恢復(fù)完整備份的數(shù)據(jù)和前一天差異備份的數(shù)據(jù)即可，不需要去恢復(fù)每一天的事務(wù)日志備份，恢復(fù)的時(shí)間會(huì)比較短。

8.1備份和恢復(fù)概述如果數(shù)據(jù)庫(kù)里的數(shù)據(jù)變動(dòng)得比較頻繁，損失一個(gè)小時(shí)的數(shù)據(jù)都是十分嚴(yán)重的損失時(shí)，用上面的辦法備份數(shù)據(jù)就不可行了，此時(shí)可以交替使用三種備份方式來(lái)備份數(shù)據(jù)庫(kù)。

例如，每天下班時(shí)做一次完整備份，在兩次完整備份之間每隔八小時(shí)做一次差異備份，在兩次差異備份之間每隔一小時(shí)做一次事務(wù)日志備份。如此一來(lái)，一旦數(shù)據(jù)損壞可以將數(shù)據(jù)恢復(fù)到最近一個(gè)小時(shí)以內(nèi)的狀態(tài)，同時(shí)又能減少數(shù)據(jù)庫(kù)備份數(shù)據(jù)的時(shí)間和備份數(shù)據(jù)文件的大小。

在前面還提到過(guò)當(dāng)數(shù)據(jù)庫(kù)文件過(guò)大不易備份時(shí)，可以分別備份數(shù)據(jù)庫(kù)文件或文件組，將一個(gè)數(shù)據(jù)庫(kù)分多次備份。在現(xiàn)實(shí)操作中，還有一種情況可以使用到數(shù)據(jù)庫(kù)文件的備份。例如在一個(gè)數(shù)據(jù)庫(kù)中，某些表里的數(shù)據(jù)變動(dòng)得很少，而某些表里的數(shù)據(jù)卻經(jīng)常改變，那么可以考慮將這些數(shù)據(jù)表分別存儲(chǔ)在不同的文件或文件組里，然后通過(guò)不同的備份頻率來(lái)備份這些文件和文件組。但使用文件和文件組來(lái)進(jìn)行備份，還原數(shù)據(jù)時(shí)也要分多次才能將整個(gè)數(shù)據(jù)庫(kù)還原完畢，所以除非數(shù)據(jù)庫(kù)文件大到備份困難，否則不要使用該備份方式。

8.1備份和恢復(fù)概述8.2.1備份設(shè)備數(shù)據(jù)庫(kù)備份設(shè)備是指用來(lái)存儲(chǔ)備份數(shù)據(jù)的存儲(chǔ)介質(zhì)，常用的備份設(shè)備類(lèi)型包括磁盤(pán)和磁帶等。磁盤(pán)：以硬盤(pán)或其它磁盤(pán)類(lèi)設(shè)備為存儲(chǔ)介質(zhì)。磁盤(pán)備份設(shè)備就可以存儲(chǔ)在本地機(jī)器上，也可以存儲(chǔ)在網(wǎng)絡(luò)的遠(yuǎn)程磁盤(pán)上。如果數(shù)據(jù)備份存儲(chǔ)在本地機(jī)器上，在由于存儲(chǔ)介質(zhì)故障或服務(wù)器崩潰而造成數(shù)據(jù)丟失的情況下，備份就沒(méi)有意義了。因此，要及時(shí)將備份文件復(fù)制到遠(yuǎn)程磁盤(pán)上。如果采用遠(yuǎn)程磁盤(pán)作為備份設(shè)備，要采用統(tǒng)一命名方式（UNC）來(lái)表示備份文件，即“\\遠(yuǎn)程服務(wù)器名\共享文件名\路徑名\文件名”。磁帶：使用磁帶作為存儲(chǔ)介質(zhì)，必須將磁帶物理地安裝在運(yùn)行SQL

Server的計(jì)算機(jī)上，磁帶備份不支持網(wǎng)絡(luò)遠(yuǎn)程備份。8.2備份操作和備份命令對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份時(shí)，備份設(shè)備可以采用物理設(shè)備名稱和邏輯設(shè)備名稱兩種方式。

物理設(shè)備名稱：即操作系統(tǒng)文件名，直接采用備份文件在磁盤(pán)上以文件方式存儲(chǔ)的完整路徑名，例如“D:\backup\data_full.bak”。

邏輯設(shè)備名稱：為物理備份設(shè)備指定的可選的邏輯別名。使用邏輯設(shè)備名稱可以簡(jiǎn)化備份路徑。8.2.1備份設(shè)備創(chuàng)建備份設(shè)備有兩種方法：使用圖形向?qū)Х绞交蚴褂孟到y(tǒng)存儲(chǔ)過(guò)程sp_addumpdevice。（1）使用系統(tǒng)存儲(chǔ)過(guò)程創(chuàng)建命名備份設(shè)備。執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_addumpdevice可以在磁盤(pán)或磁帶上創(chuàng)建命名備份設(shè)備?！纠?.1】

在本地硬盤(pán)上創(chuàng)建一個(gè)備份設(shè)備。USEmasterGOEXECsp_addumpdevice'disk','mybackupfile',‘E:\data\mybackupfile.bak’8.2.1備份設(shè)備8.2.1備份設(shè)備（2）使用對(duì)象資源管理器創(chuàng)建永久備份設(shè)備。1）打開(kāi)【對(duì)象資源管理器】，在【服務(wù)器對(duì)象】節(jié)點(diǎn)下找到【備份設(shè)備】節(jié)點(diǎn)，單擊鼠標(biāo)右鍵，彈出右鍵菜單。2）選擇【新建備份設(shè)備】菜單，彈出新建備份設(shè)備窗口。3）輸入備份設(shè)備邏輯名稱，并指定備份設(shè)備的物理路徑，單擊【確定】即可。8.2.2備份命令1．備份整個(gè)數(shù)據(jù)庫(kù)語(yǔ)法格式：BACKUPDATABASEdatabase_name /*被備份的數(shù)據(jù)庫(kù)名*/TO<backup_device>[,...n] /*指出備份目標(biāo)設(shè)備*/[WITH

[BLOCKSIZE={blocksize|@blocksize_variable}] /*塊大小*/

[[,]{CHECKSUM|NO_CHECKSUM}]

[[,]{STOP_ON_ERROR|CONTINUE_AFTER_ERROR}]

[[,]DESCRIPTION={'text'|@text_variable}]

[[,]DIFFERENTIAL]

[[,]EXPIREDATE={date|@date_var} /*備份集到期和允許被重寫(xiě)的日期*/

|RETAINDAYS={days|@days_var}]

[[,]PASSWORD={password|@password_variable}]

[[,]{FORMAT|NOFORMAT}]

[[,]{INIT|NOINIT}] /*指定是覆蓋還是追加*/

[[,]{NOSKIP|SKIP}]

[[,]MEDIADESCRIPTION={'text'|@text_variable}]

[[,]MEDIANAME={media_name|@media_name_variable}]

[[,]MEDIAPASSWORD={mediapassword|@mediapassword_variable}]

[[,]NAME={backup_set_name|@backup_set_name_var}]

[[,]STATS[=percentage]]

[[,]COPY_ONLY]]8.2.2備份命令【例8.2】

使用邏輯名test1在E盤(pán)中創(chuàng)建一個(gè)命名的備份設(shè)備，并將數(shù)據(jù)庫(kù)PXSCJ完全備份到該設(shè)備。USEmasterGOEXECsp_addumpdevice'disk','test1','E:\data\test1.bak'BACKUPDATABASEPXSCJTOtest18.2.2備份命令本例的執(zhí)行結(jié)果如圖所示。8.2.2備份命令【例8.3】

將數(shù)據(jù)庫(kù)PXSCJ備份到多個(gè)備份設(shè)備。EXECsp_addumpdevice'disk','test2','E:\data\test2.bak'EXECsp_addumpdevice'disk','test3','E:\data\test3.bak'BACKUPDATABASEPXSCJTOtest2,test3 WITHNAME='pxscjbk'

8.2.2備份命令2．差異備份數(shù)據(jù)庫(kù)語(yǔ)法格式：BACKUPDATABASE{database_name|@database_name_var} READ_WRITE_FILEGROUPS [,FILEGROUP={logical_filegroup_name|@logical_filegroup_name_var}[,...n]]TO<backup_device>[,…n][[MIRRORTO<backup_device>[,...n]][...next-mirror]][WITH {[[,]DIFFERENTIAL] /*其余選項(xiàng)與數(shù)據(jù)庫(kù)的完全備份相同*/ }]【例8.4】

將PXSCJ在test2,test3上進(jìn)行差異備份。BACKUPDATABASEPXSCJTO

test2,test3WITHDIFFERENTIAL8.2.2備份命令3．事務(wù)日志備份將事務(wù)日志中從前一次成功備份結(jié)束位置開(kāi)始，到當(dāng)前事務(wù)日志結(jié)尾處的內(nèi)容進(jìn)行備份。進(jìn)行事務(wù)日志備份使用BACKUPLOG語(yǔ)句。語(yǔ)法格式：BACKUPLOG{database_name|@database_name_var} /*指定被備份的數(shù)據(jù)庫(kù)名*/{TO<backup_device>[,...n] /*指定備份目標(biāo)*/[[MIRRORTO<backup_device>[,...n]][...next-mirror]][WITH { {NORECOVERY|STANDBY=undo_file_name} |NO_TRUNCATE] |/*其余選項(xiàng)與數(shù)據(jù)庫(kù)的完全備份相同*/ }}8.2.2備份命令【例8.5】

創(chuàng)建一個(gè)命名的備份設(shè)備PXSCJLOGBK，并備份PXSCJ數(shù)據(jù)庫(kù)的事務(wù)日志。EXECsp_addumpdevice'disk','PXSCJLOGBK','E:\data\testlog.bak'BACKUPLOGPXSCJTOPXSCJLOGBK或者也可以將PXSCJ的事務(wù)日志繼續(xù)備份到test2,test3上：BACKUPLOGPXSCJTOtest2,test38.2.3使用對(duì)象資源管理器備份在SQLServerManagementStudio中進(jìn)行備份的步驟如下。第1步：?jiǎn)?dòng)SQLServerManagementStudio，在對(duì)象資源管理器中選擇“管理”，右擊鼠標(biāo)，如圖所示，在彈出的快捷菜單上選擇“備份”菜單項(xiàng)。第2步：在打開(kāi)的“備份數(shù)據(jù)庫(kù)”窗口中選擇要備份的數(shù)據(jù)庫(kù)名。8.2.3使用對(duì)象資源管理器備份第3步：選擇了數(shù)據(jù)庫(kù)之后，窗口最下方的目標(biāo)欄中會(huì)列出與PXSCJ數(shù)據(jù)庫(kù)相關(guān)的備份設(shè)備。第4步：在“備份數(shù)據(jù)庫(kù)”窗口中，將不需要的備份目標(biāo)選擇后單擊“刪除”按鈕刪除，最后備份目標(biāo)選擇為“mybackupfile”，單擊“確定”按鈕，執(zhí)行備份操作。8.2.3使用對(duì)象資源管理器備份8.3恢復(fù)操作和恢復(fù)命令8.3.1檢查點(diǎn)SQLServer系統(tǒng)在日志中存儲(chǔ)有關(guān)信息，以便在需要時(shí)可以恢復(fù)（前滾）或撤銷(xiāo)（回滾）構(gòu)成事務(wù)的數(shù)據(jù)修改。SQLServer系統(tǒng)對(duì)修改過(guò)的數(shù)據(jù)緩沖區(qū)的內(nèi)容并不是立即寫(xiě)回磁盤(pán)，而是控制寫(xiě)入磁盤(pán)的時(shí)間，它將在緩沖區(qū)內(nèi)修改過(guò)的數(shù)據(jù)頁(yè)存入高速緩存一段時(shí)間后再寫(xiě)入磁盤(pán)，從而實(shí)現(xiàn)優(yōu)化磁盤(pán)寫(xiě)入。SQLServer系統(tǒng)定期將所有臟日志和數(shù)據(jù)頁(yè)刷新到磁盤(pán)，這就稱為檢查點(diǎn)。SQLServer恢復(fù)機(jī)制能夠通過(guò)檢查點(diǎn)在檢查事務(wù)日志時(shí)保證數(shù)據(jù)庫(kù)的一致性，在對(duì)事務(wù)日志進(jìn)行檢查時(shí)，系統(tǒng)將從最后一個(gè)檢查點(diǎn)開(kāi)始檢查事務(wù)日志，以發(fā)現(xiàn)數(shù)據(jù)庫(kù)中所有數(shù)據(jù)的改變。8.3.2數(shù)據(jù)庫(kù)的恢復(fù)命令1．恢復(fù)整個(gè)數(shù)據(jù)庫(kù)【例8.6】

使用RESTORE語(yǔ)句從一個(gè)已存在的命名備份介質(zhì)PXSCJBK1中恢復(fù)整個(gè)數(shù)據(jù)庫(kù)PXSCJ。首先創(chuàng)建備份設(shè)備PXSCJBK1：EXECsp_addumpdevice'disk','PXSCJBK1', 'E:\data\PXSCJBK1.bak'使用BACKUP命令對(duì)PXSCJ數(shù)據(jù)進(jìn)行完全備份：BACKUPDATABASEPXSCJ TOPXSCJBK18.3.2數(shù)據(jù)庫(kù)的恢復(fù)命令接著，在恢復(fù)數(shù)據(jù)庫(kù)之前，用戶可以對(duì)PXSCJ數(shù)據(jù)庫(kù)做一些修改，例如刪除其中一個(gè)表，以便確認(rèn)是否恢復(fù)了數(shù)據(jù)庫(kù)?；謴?fù)數(shù)據(jù)庫(kù)的命令如下：RESTOREDATABASEPXSCJ FROMPXSCJBK1 WITHFILE=1,REPLACE執(zhí)行結(jié)果如圖所示。2．恢復(fù)差異數(shù)據(jù)庫(kù)備份【例8.7】恢復(fù)PXSCJ的差異備份。

BACKUPLOGPXSCJTOtest2,test3WITHNORECOVERY--尾日志備份，進(jìn)入還原狀態(tài)GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=1,NORECOVERY--還原完整備份GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=2,REPLACE--還原差異備份GO8.3.2數(shù)據(jù)庫(kù)的恢復(fù)命令8.3.2數(shù)據(jù)庫(kù)的恢復(fù)命令3．恢復(fù)事務(wù)日志【例8.8】恢復(fù)例8.5中PXSCJ在test2,test3上的日志備份。BACKUPLOGPXSCJTOtest2,test3WITHNORECOVERY--尾日志備份，進(jìn)入還原狀態(tài)GORESTOREDATABASEPXSCJ FROMtest2,test3 WITHFILE=1,NORECOVERY--還原完整備份GORESTORELOGPXSCJ FROMtest2,test3 WITHFILE=2,NORECOVERY--還原日志備份GORESTORELOGPXSCJ FR