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

第8章

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

SQL

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

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

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

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

◆簡單恢復(fù)模式。在該模式下，數(shù)據(jù)庫會自動把不活動的日志刪除，因此簡化了備份的還原，但因為沒有事務(wù)日志備份，所以不能恢復(fù)到失敗的時間點。通常，此模式只用于對數(shù)據(jù)庫數(shù)據(jù)安全要求不太高的數(shù)據(jù)庫。并且在該模式下，數(shù)據(jù)庫只能做完整和差異備份。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[[,]{CHECKSUM|NO_CHECKSUM}]

[[,]{STOP_ON_ERROR|CONTINUE_AFTER_ERROR}]

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

[[,]DIFFERENTIAL]

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

|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盤中創(chuàng)建一個命名的備份設(shè)備，并將數(shù)據(jù)庫PXSCJ完全備份到該設(shè)備。USEmasterGOEXECsp_addumpdevice'disk','test1','E:\data\test1.bak'BACKUPDATABASEPXSCJTOtest18.2.2備份命令本例的執(zhí)行結(jié)果如圖所示。8.2.2備份命令【例8.3】

將數(shù)據(jù)庫PXSCJ備份到多個備份設(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ù)庫語法格式：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] /*其余選項與數(shù)據(jù)庫的完全備份相同*/ }]【例8.4】

將PXSCJ在test2,test3上進行差異備份。BACKUPDATABASEPXSCJTO

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

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

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

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