sql數(shù)據(jù)庫備份和還原

1、SQL 備份與還原經(jīng)典文檔SQL 備份1、SQL 數(shù)據(jù)庫恢復(fù)模型 1)完全恢復(fù)模型(1)備份時要備份數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件(2)還原時使用數(shù)據(jù)庫的備份的數(shù)據(jù)文件副本和全部日志信息來恢復(fù)數(shù)據(jù)庫。(3)能還原全部數(shù)據(jù)，并可以將數(shù)據(jù)庫恢復(fù)到任意指定的時刻。(4)為保證實現(xiàn)即時點恢復(fù)，對數(shù)據(jù)庫的所有*作都將完整地記入日志，這樣，日志占用空間較大，對性能也有所影響。(2)大容量日志記錄恢復(fù)模型(1)備份時要備份數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件(2)還原時使用數(shù)據(jù)庫的備份的數(shù)據(jù)文件副本和全部日志信息來恢復(fù)數(shù)據(jù)庫。(3)日志中不記錄*作細(xì)節(jié)(如 selectinto、createindex 等)，而只記錄*作

2、的最終結(jié)果，因此占用日志空間小。(4)只支持將數(shù)據(jù)庫還原到事務(wù)日志備份的時刻，而不支持即時點恢復(fù)，因此可能產(chǎn)生數(shù)據(jù)丟失。(3)簡單恢復(fù)模型(1)備份時只備份數(shù)據(jù)文件，還原時也用備份的數(shù)據(jù)文件恢復(fù)數(shù)據(jù)庫。(2)只能將數(shù)據(jù)恢復(fù)到數(shù)據(jù)文件備份的時刻，可能產(chǎn)生最多的數(shù)據(jù)丟失。(3)不適于生產(chǎn)系統(tǒng)和大規(guī)模*作環(huán)境下選用。alterdatabased1setrecoverysimple-設(shè)置數(shù)據(jù)庫恢復(fù)模型alterdatabased1setrecoverybulk_loggedalterdatabased1setrecoveryfull2、備份設(shè)備 1)物理設(shè)備 disk：支持本地磁盤或者網(wǎng)絡(luò)備份tape

3、：支持磁帶機(jī)備份namepipe：支持第三方備份軟件 2)邏輯設(shè)備永久備份文件：可以重復(fù)使用，應(yīng)該在備份前創(chuàng)建。臨時備份文件：用于一次性備份，在備份時創(chuàng)建。execsp_addumpdevicedisk,bak2,e:back_devicebak2.bak-創(chuàng)建永久磁盤備份設(shè)備execsp_addumpdevicedisk,bak3,e:back_devicebak3.bakexecsp_addumpdevicedisk,bak4,sv2backupbak4.bak-創(chuàng)建網(wǎng)絡(luò)永久磁盤備份設(shè)備execsp_addumpdevicedisk,bak5,sv2backupbak5.bakexecsp

4、_dropdevicebak5-刪除備份設(shè)備backupdatabased3tobak3-將數(shù)據(jù)庫備份到備份設(shè)備backupdatabased4tobak4restoreheaderonlyfrombak2-查看備份設(shè)備中的內(nèi)容backupdatabased3todisk=e:back_filed3.bak-將數(shù)據(jù)庫備份到臨時備份文件backupdatabased4todisk=e:back_filed4.bakrestoredatabased3frombak3-從備份設(shè)備還原數(shù)據(jù)庫restoredatabased4fromdisk=e:back_filed4.bak-從備份文件還原數(shù)據(jù)庫 3

5、、 使用多個備份文件存儲備份 1）SQL 可同時向多個備份文件進(jìn)行寫*作。如果把這些文件放到多個磁帶機(jī)或磁盤中，則可提高備份速度。2）這多個備份文件必須用同業(yè)型的媒體，并放到一個媒體集中。3）媒體集中的文件必須同時使用，而不能單獨使用。4）可以通過 format 命令將媒體集重新劃分，但原備份集中的數(shù)據(jù)不能再使用。backupdatabased4tobak4,bak5,bak6withmedianame=bak456,format-備份 D4 并形成 MediaSetbackupdatabased3tobak4-失敗，因 Mediaset 中文件必須同時使用backupdatabased3to

6、bak4,bak5,bak6-成功，將 D3 也備份到 MediaSet 中restoreheaderonlyfrombak4,bak5,bak6-查看 MediaSet 中的備份內(nèi)容backupdatabased4tobak4withmedianame=bak4,format-重新戈 U 分 MediaSetbackupdatabased3tobak5,bak6withmedianame=bak56,formatbackupdatabased1tobak1withinit-withinit 重寫備份設(shè)備中內(nèi)容backupdatabased2tobak1withnoinit-withnoini

7、t 將內(nèi)容追加到備份設(shè)備中 restoreheaderonlyfrombak14、備份的方法 1)完全備份(1)是備份的基準(zhǔn)。在做備份時第一次備份都建議使用完全備份。(2)完全備份會備份數(shù)據(jù)庫的所有數(shù)據(jù)文件、數(shù)據(jù)對象和數(shù)據(jù)。(3)會備份事務(wù)日志中任何未提交的事務(wù)。因為已提交的事務(wù)已經(jīng)寫入數(shù)據(jù)文件中。backupdatabaseditobaklwithinit-完全備份 backupdatabaseditobakiwithnoinit2)差異備份(i)基于完全備份。(2)備份自最近一次完全備份以來的所有數(shù)據(jù)庫改變。(3)恢復(fù)時，只應(yīng)用最近一次完全備份和最新的差異備份。backupdatabase

8、d2tobak2withinit,name=d2_full-差異備份，第一次備份時應(yīng)做完全備份createtablebi(ciintnotnull,c2char(10)notnull)backupdatabased2tobak2withdifferential,name=d2_diffiinsertbivalues(i,a)backupdatabased2tobak2withdifferential,name=d2_diff2insertbivalues(2,b)backupdatabased2tobak2withdifferential,name=d2_diff3insertbivalues

9、(3,c)backupdatabased2tobak2withdifferential,name=d2_diff4restoreheaderonlyfrombak23)事務(wù)日志備份(i)基于完全備份。(2)為遞增備份，即備份從上一次備份以來到備份時所寫的事務(wù)日志。(3)允許恢復(fù)到故障時刻或者一個強(qiáng)制時間點。(4)恢復(fù)時，需要應(yīng)用完全備份和完全備份后的每次日志備份。backupdatabased3tobak3withinit,name=d3_full-日志備份，第一次備份時應(yīng)做完全備份createtablebi(ciintnotnull,c2char(i0)notnull)backuplogd3

10、tobak3withinsertbivalues(i,a)backuplogd3tobak3withinsertbivalues(2,b)backuplogd3tobak3withinsertbivalues(3,c)backuplogd3tobak3withrestoreheaderonlyfrombak3createtableb1(c1intnotnull,c2char(10)notnull)-Full+Log+Diffbackuplogd4tobak4withinsertb1values(1,a)backuplogd4tobak4withinsertb1values(2,b)backup

11、databased4tobak4withdifferential,name=d4_diff1insertb1values(3,c)backuplogd4tobak4withinsertb1values(4,d)backuplogd4tobak4withinsertb1values(5,d)backupdatabased4tobak4withdifferential,name=d4_diff2restoreheaderonlyfrombak4日志清除1)如果日志空間被填滿，數(shù)據(jù)庫將不能記錄修改。2)數(shù)據(jù)庫在做完全備份時日志被截斷。3)如果將Translogoncheckpoint選項設(shè)為 TRU

12、E,則結(jié)果為不保存日志，即沒有日志記錄,不建議使用。4) withtruncate_only 和 withno_log 設(shè)置日志滿時清除日志5) withno_truncate 則可以完整保存日志，不清除，即使在數(shù)據(jù)文件已經(jīng)損壞情況下。主要用于數(shù)據(jù)庫疝問題后在恢復(fù)前使用?？梢詫?shù)據(jù)還原到出故障的那一時刻。execsp_dboptiond3execsp_dboptionsp_dboptiond3,trunc.logonchkpt.,true-設(shè)置自動清除數(shù)據(jù)庫日志sp_dboptiond3,trunc.logonchkpt.,false-將自動清除數(shù)據(jù)庫日志的選項去除backuplogd4wit

13、htruncate_only-設(shè)置 D4 日志滿時清除日志，并做清除記錄backuplogd4withno_log-設(shè)置 D4 日志滿時清除日志，但不做清除記錄backuplogd4tobak4withno_truncate-在 D4 數(shù)據(jù)庫損壞時馬上備份當(dāng)前數(shù)據(jù)庫日志(DEMO)使用 no_truncate完全+修改 1+差異+修改 2+差異+修改 3+停止 SQL,刪除數(shù)據(jù)庫數(shù)據(jù)文件+重啟 SQLbackuplogno_truncate再還原，可還原到修改 34)文件/文件組備份(1)用于超大型數(shù)據(jù)庫。(2)只備份選定的文件或者文件組。(3)必須同時作日志備份。(4)還原時用文件/文件組備

14、份和日志備份進(jìn)行還原。(5)備份量少，恢復(fù)速度快。createdatabased5onprimary(name=d5_data1,filename=e:datad5d5_data1.mdf,size=2MB),filegroupFG2 一創(chuàng)建數(shù)據(jù)庫時創(chuàng)建 filegroupFG2(name=d5_data2,filename=e:datad5d5_data2.ndf,一并將文件 d5_data2 放至 UFG2 中size=2Mb)logon(name=d5_log1,filename=e:datad5d5_log1.ldf,size=2Mb)used5goalterdatabased5add

15、file(name=d5_data3,filename=e:datad5d5_data5.ndf,size=2MB)tofilegroupFG2將 d5_data3 加到文件組 FG2 中alterdatabased5addfilegroupFG3 一增加文件組 FG3alterdatabased5-4 各 d5_data4 力口至 U 文件組 FG2 中addfile(name=d5_data4,filename=e:datad5d5_data4.ndf,size=2MB)tofilegroupFG3sp_helpdbd5createtablet1(c1intnotnull,c2char(1

16、0)notnull)onprimary-將不同表放到不同 filegroup 中createtablet2(c1intnotnull,c2char(10)notnull)onFG2createtablet3(c1intnotnull,c2char(10)notnull)onFG3backupdatabased5tobak5withinit,name=d5_full-filegroup 備份backupdatabased5filegroup=primarytobak5withbackuplogd5tobak5withbackupdatabased5filegroup=FG2tobak5withb

17、ackuplogd5tobak5withbackupdatabased5filegroup=FG3tobak5withbackuplogd5tobak5withbackupdatabased5tobak6withinit,name=d5_full-file 備份backupdatabased5file=d5_data1tobak6withbackuplogd5tobak6withbackupdatabased5file=d5_data2tobak6withbackuplogd5tobak6withbackupdatabased5file=d5_data3tobak6withbackuplogd

18、5tobak6withbackupdatabased5file=d5_data4tobak6withbackuplogd5tobak6withrestoreheaderonlyfrombak6SQL 還原 1、驗證備份 restoreheaderonlyfrombak3restorefilelistonlyfrombak3withfile=1restorelabelonlyfrombak3restoreverifyonlyfrombak32、從備份中還原 restoreheaderonlyfrombak1restoredatabased1frombak1withfile=2 一從完全備份中恢復(fù)

19、restoreheaderonlyfrombak2 一從差異備份中恢復(fù)restoredatabased2frombak2withfile=1,norecoveryrestoredatabased2frombak2withfile=5,recoveryrestoreheaderonlyfrombak3 一從日志備份中恢復(fù)restoredatabased3frombak3withfile=1,norecoveryrestorelogd3frombak3withfile=2,norecoveryrestorelogd3frombak3withfile=3,norecoveryrestorelogd3

20、frombak3withfile=4,norecoveryrestorelogd3frombak3withfile=5,recoveryrestoredatabased3frombak3withfile=1,norecoveryrestorelogd3frombak3withfile=2,norecoveryrestorelogd3frombak3withfile=3,norecoveryrestorelogd3frombak3withfile=4,recovery,stopat=2003-08-1511:29:00.000,restoredatabased5filegroup=FG2from

21、bak5withfile=4,norecovery-還原文件組備份restorelogd5frombak5withfile=5,norecoveryrestorelogd5frombak5withfile=7,recoveryrestoreheaderonlyfrombak6-還原文件備份restoredatabased5file=d5_data3frombak6withfile=6,norecoveryrestorelogd5frombak6withfile=7,norecoveryrestorelogd5frombak6withfile=9,recoveryrestoredatabased

22、5frombak6withreplace-刪除現(xiàn)有數(shù)據(jù)庫，從備份中重建數(shù)據(jù)庫createdatabased6-moveto 將數(shù)據(jù)庫文件移動到新位置onprimary(name=d6_data,filename=E:ProgramFilesMicrosoftSQLServerMSSQLdatad6_Data.MDF,size=2MB)logon(name=d6_log,filename=E:ProgramFilesMicrosoftSQLServerMSSQLdatad6_log.ldf,size=2MB)go-恢復(fù)到指定時間backupdatabased6tobak6withinitdrop

23、databased6restoredatabased6frombak6withmoved6_datatoe:datad6d6_data.mdf,moved6_logtoe:datad6d6_log.ldfsp_helpdbd63、分離與重連接數(shù)據(jù)庫 sp_detach_dbd6sp_attach_dbd6,e:datad6d6_data.mdf,e:datad6d6_log.ldfsp_detach_dbd6gocreatedatabased6onprimary(filename=e:datad6d6_data.mdf)forattachgo4、恢復(fù)損壞的系統(tǒng)數(shù)據(jù)庫1)先備份 MASTER、M

24、SDB2)停止 SQL 服務(wù)，將 MASTER 數(shù)據(jù)庫文件刪除或者重命名。這樣，SQL 服務(wù)將不能啟動。3)系統(tǒng)數(shù)據(jù)庫的還原(1)如果 SQL 服務(wù)還能啟動，則從備份中恢復(fù)系統(tǒng)數(shù)據(jù)庫。(2)如果 SQL 服務(wù)不能啟動，則需要重建系統(tǒng)數(shù)據(jù)庫。使用 SQL 文件夾 TOOLSBINN 目錄下的 Rebuildm.exe 重建 master 數(shù)據(jù)庫。(3)創(chuàng)建備份設(shè)備，指向以前的備份設(shè)備。(4)以單用戶模式啟動 SQLcdprogramefilesmicrosoftsqlservermssqlbinnsqlservr.exe-c-m(5)進(jìn)查詢分析器，從備份中恢復(fù) master 數(shù)據(jù)庫。restor

25、edatabasemasterfrommasterbakrestoredatabasemsdbfromdisk=e:bakmsdb.bakMASTER 還原后，SQL 中用戶數(shù)據(jù)庫的信息也會恢復(fù)。(6)如果 MASTER 沒有備份，則需要用 sp_attach_db 命令將用戶數(shù)據(jù)庫附加到新的 MASTER數(shù)據(jù)庫中。全備份+差異備份：以一周為周期，星期日進(jìn)行全備份，星期一到星期六每天進(jìn)行差異備份。全備份十日志備份：以一周為周期，星期日進(jìn)行全備份，星期一到星期六每天進(jìn)行日志備份。文件組備份+日志備份：備份周期取決于數(shù)據(jù)庫的大小和能力，每周期分別進(jìn)行一部分?jǐn)?shù)據(jù)文件備份，每天進(jìn)行日志備份。手動創(chuàng)建備份：使用 T_SQL 語句創(chuàng)建備份設(shè)備。在 SQLServer 中使用 sp_addumpdevice 語句創(chuàng)建備份設(shè)備，其語法形式如下：sp_addumpdevice 磁盤類型，備份設(shè)備的邏輯名稱，備份設(shè)備的物理名稱例：創(chuàng)建備份設(shè)備 zwbackusemasterExecsp_addumpdevicedisk；zwback:cback.bak-創(chuàng)建備份設(shè)備execsp_dropdevicezwback-刪除備份設(shè)備T-SQL 創(chuàng)建完全備份語法：