SQL備份與還原經(jīng)典文檔

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

2、錄*作的最終結(jié)果，因此占用日志空間小。（4）只支持將數(shù)據(jù)庫(kù)還原到事務(wù)日志備份的時(shí)刻，而不支持即時(shí)點(diǎn)恢復(fù)，因此可能產(chǎn)生數(shù)據(jù)丟失。-（3）簡(jiǎn)單恢復(fù)模型-（1）備份時(shí)只備份數(shù)據(jù)文件，還原時(shí)也用備份的數(shù)據(jù)文件恢復(fù)數(shù)據(jù)庫(kù)。（2）只能將數(shù)據(jù)恢復(fù)到數(shù)據(jù)文件備份的時(shí)刻，可能產(chǎn)生最多的數(shù)據(jù)丟失。（3）不適于生產(chǎn)系統(tǒng)和大規(guī)模*作環(huán)境下選用。-alter database d1 set recovery simple -設(shè)置數(shù)據(jù)庫(kù)恢復(fù)模型alter database d1 set recovery bulk_loggedalter database d1 set recovery full-2、備份設(shè)備-1）物理設(shè)

3、備-disk：支持本地磁盤或者網(wǎng)絡(luò)備份tape：支持磁帶機(jī)備份name pipe：支持第三方備份軟件-2）邏輯設(shè)備-永久備份文件：可以重復(fù)使用，應(yīng)該在備份前創(chuàng)建。臨時(shí)備份文件：用于一次性備份，在備份時(shí)創(chuàng)建。-exec sp_addumpdevice 'disk','bak2','e:back_devicebak2.bak' -創(chuàng)建永久磁盤備份設(shè)備exec sp_addumpdevice 'disk','bak3','e:back_devicebak3.bak'-exec sp_addumpdevic

4、e 'disk','bak4','sv2backupbak4.bak' -創(chuàng)建網(wǎng)絡(luò)永久磁盤備份設(shè)備exec sp_addumpdevice 'disk','bak5','sv2backupbak5.bak'-exec sp_dropdevice 'bak5' -刪除備份設(shè)備-backup database d3 to bak3 -將數(shù)據(jù)庫(kù)備份到備份設(shè)備backup database d4 to bak4-restore headeronly from bak2 -查看備份設(shè)備中的內(nèi)容

5、-backup database d3 to disk='e:back_filed3.bak' -將數(shù)據(jù)庫(kù)備份到臨時(shí)備份文件backup database d4 to disk='e:back_filed4.bak'-restore database d3 from bak3 -從備份設(shè)備還原數(shù)據(jù)庫(kù) restore database d4 from disk='e:back_filed4.bak' -從備份文件還原數(shù)據(jù)庫(kù)-3、使用多個(gè)備份文件存儲(chǔ)備份-1）SQL可同時(shí)向多個(gè)備份文件進(jìn)行寫*作。如果把這些文件放到多個(gè)磁帶機(jī)或磁盤中，則可提高備份速度

6、。2）這多個(gè)備份文件必須用同業(yè)型的媒體，并放到一個(gè)媒體集中。3）媒體集中的文件必須同時(shí)使用，而不能單獨(dú)使用。4）可以通過format命令將媒體集重新劃分，但原備份集中的數(shù)據(jù)不能再使用。-backup database d4 to bak4,bak5,bak6 with medianame='bak456',format -備份D4并形成Media Setbackup database d3 to bak4 -失敗，因Media set中文件必須同時(shí)使用backup database d3 to bak4,bak5,bak6 -成功，將D3也備份到Media Set中restor

7、e headeronly from bak4,bak5,bak6-查看Media Set中的備份內(nèi)容-backup database d4 to bak4 with medianame='bak4',format -重新劃分Media Setbackup database d3 to bak5,bak6 with medianame='bak56',format-backup database d1 to bak1 with init -with init重寫備份設(shè)備中內(nèi)容backup database d2 to bak1 with noinit -with

8、noinit將內(nèi)容追加到備份設(shè)備中restore headeronly from bak1-4、備份的方法-1）完全備份-（1）是備份的基準(zhǔn)。在做備份時(shí)第一次備份都建議使用完全備份。（2）完全備份會(huì)備份數(shù)據(jù)庫(kù)的所有數(shù)據(jù)文件、數(shù)據(jù)對(duì)象和數(shù)據(jù)。（3）會(huì)備份事務(wù)日志中任何未提交的事務(wù)。因?yàn)橐烟峤坏氖聞?wù)已經(jīng)寫入數(shù)據(jù)文件中。-backup database d1 to bak1 with init -完全備份backup database d1 to bak1 with noinit-2）差異備份-（1）基于完全備份。（2）備份自最近一次完全備份以來(lái)的所有數(shù)據(jù)庫(kù)改變。（3）恢復(fù)時(shí)，只應(yīng)用最近一次完全備份

9、和最新的差異備份。-backup database d2 to bak2 with init,name='d2_full' -差異備份，第一次備份時(shí)應(yīng)做完全備份create table b1(c1 int not null,c2 char(10) not null)backup database d2 to bak2 with differential,name='d2_diff1'insert b1 values(1,'a')backup database d2 to bak2 with differential,name='d2_di

10、ff2'insert b1 values(2,'b')backup database d2 to bak2 with differential,name='d2_diff3'insert b1 values(3,'c')backup database d2 to bak2 with differential,name='d2_diff4'restore headeronly from bak2-3）事務(wù)日志備份-（1）基于完全備份。（2）為遞增備份，即備份從上一次備份以來(lái)到備份時(shí)所寫的事務(wù)日志。（3）允許恢復(fù)到故障時(shí)刻或者

11、一個(gè)強(qiáng)制時(shí)間點(diǎn)。（4）恢復(fù)時(shí)，需要應(yīng)用完全備份和完全備份后的每次日志備份。-backup database d3 to bak3 with init,name='d3_full' -日志備份，第一次備份時(shí)應(yīng)做完全備份create table b1(c1 int not null,c2 char(10) not null)backup log d3 to bak3 withinsert b1 values(1,'a')backup log d3 to bak3 withinsert b1 values(2,'b')backup log d3 to

12、bak3 withinsert b1 values(3,'c')backup log d3 to bak3 withrestore headeronly from bak3-create table b1(c1 int not null,c2 char(10) not null) -Full+Log+Diffbackup log d4 to bak4 withinsert b1 values(1,'a')backup log d4 to bak4 withinsert b1 values(2,'b')backup database d4 to b

13、ak4 with differential,name='d4_diff1'insert b1 values(3,'c')backup log d4 to bak4 withinsert b1 values(4,'d')backup log d4 to bak4 withinsert b1 values(5,'d')backup database d4 to bak4 with differential,name='d4_diff2'restore headeronly from bak4-日志清除-1）如果日志空間

14、被填滿，數(shù)據(jù)庫(kù)將不能記錄修改。2）數(shù)據(jù)庫(kù)在做完全備份時(shí)日志被截?cái)唷?）如果將'Trans log on checkpoint'選項(xiàng)設(shè)為TRUE，則結(jié)果為不保存日志，即沒有日志記錄，不建議使用。4）with truncate_only和with no_log設(shè)置日志滿時(shí)清除日志5）with no_truncate則可以完整保存日志，不清除，即使在數(shù)據(jù)文件已經(jīng)損壞情況下。主要用于數(shù)據(jù)庫(kù)出問題后在恢復(fù)前使用。可以將數(shù)據(jù)還原到出故障的那一時(shí)刻。-exec sp_dboption d3exec sp_dboptionsp_dboption 'd3','trunc.

15、 log on chkpt.','true' -設(shè)置自動(dòng)清除數(shù)據(jù)庫(kù)日志sp_dboption 'd3','trunc. log on chkpt.','false' -將自動(dòng)清除數(shù)據(jù)庫(kù)日志的選項(xiàng)去除-backup log d4 with truncate_only -設(shè)置D4日志滿時(shí)清除日志，并做清除記錄-backup log d4 with no_log -設(shè)置D4日志滿時(shí)清除日志，但不做清除記錄-backup log d4 to bak4 with no_truncate-在D4數(shù)據(jù)庫(kù)損壞時(shí)馬上備份當(dāng)前數(shù)據(jù)庫(kù)日志(DE

16、MO)-使用no_truncate完全+修改1+差異+修改2+差異+修改3+停止SQL，刪除數(shù)據(jù)庫(kù)數(shù)據(jù)文件+重啟SQLbackup log no_truncate再還原，可還原到修改34）文件/文件組備份-（1）用于超大型數(shù)據(jù)庫(kù)。（2）只備份選定的文件或者文件組。（3）必須同時(shí)作日志備份。（4）還原時(shí)用文件/文件組備份和日志備份進(jìn)行還原。（5）備份量少，恢復(fù)速度快。-create database d5 on primary(name=d5_data1,filename='e:datad5d5_data1.mdf',size=2MB),filegroup FG2 -創(chuàng)建數(shù)據(jù)庫(kù)時(shí)

17、創(chuàng)建filegroup FG2(name=d5_data2,filename='e:datad5d5_data2.ndf', -并將文件d5_data2放到FG2中size=2Mb)log on(name=d5_log1,filename='e:datad5d5_log1.ldf',size=2Mb)use d5goalter database d5add file(name=d5_data3,filename='e:datad5d5_data5.ndf',size=2MB)to filegroup FG2 -將d5_data3加到文件組FG2中

18、alter database d5 add filegroup FG3 -增加文件組FG3alter database d5 -將d5_data4加到文件組FG2中add file(name=d5_data4,filename='e:datad5d5_data4.ndf',size=2MB)to filegroup FG3sp_helpdb d5create table t1(c1 int not null,c2 char(10) not null) on primary -將不同表放到不同filegroup中create table t2(c1 int not null,c2

19、 char(10) not null) on FG2create table t3(c1 int not null,c2 char(10) not null) on FG3-backup database d5 to bak5 with init,name='d5_full' -filegroup備份backup database d5 filegroup='primary' to bak5 withbackup log d5 to bak5 withbackup database d5 filegroup='FG2' to bak5 withb

20、ackup log d5 to bak5 withbackup database d5 filegroup='FG3' to bak5 withbackup log d5 to bak5 with-backup database d5 to bak6 with init,name='d5_full' -file備份backup database d5 file='d5_data1' to bak6 withbackup log d5 to bak6 withbackup database d5 file='d5_data2' to

21、 bak6 withbackup log d5 to bak6 withbackup database d5 file='d5_data3' to bak6 withbackup log d5 to bak6 withbackup database d5 file='d5_data4' to bak6 withbackup log d5 to bak6 withrestore headeronly from bak6=SQL還原=1、驗(yàn)證備份-restore headeronly from bak3restore filelistonly from bak3 w

22、ith file=1restore labelonly from bak3restore verifyonly from bak3-2、從備份中還原-restore headeronly from bak1restore database d1 from bak1 with file=2 -從完全備份中恢復(fù)-restore headeronly from bak2 -從差異備份中恢復(fù)restore database d2 from bak2 with file=1,norecovery restore database d2 from bak2 with file=5,recovery-res

23、tore headeronly from bak3 -從日志備份中恢復(fù)restore database d3 from bak3 with file=1,norecoveryrestore log d3 from bak3 with file=2,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore log d3 from bak3 with file=4,norecoveryrestore log d3 from bak3 with file=5,recovery-restore database d3 from b

24、ak3 with file=1,norecovery -恢復(fù)到指定時(shí)間restore log d3 from bak3 with file=2,norecoveryrestore log d3 from bak3 with file=3,norecoveryrestore log d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000'-restore database d5 filegroup='FG2' from bak5 with file=4,norecovery -還原文件組備

25、份restore log d5 from bak5 with file=5,norecoveryrestore log d5 from bak5 with file=7,recovery-restore headeronly from bak6 -還原文件備份restore database d5 file='d5_data3' from bak6 with file=6,norecoveryrestore log d5 from bak6 with file=7,norecoveryrestore log d5 from bak6 with file=9,recovery-r

26、estore database d5 from bak6 with replace -刪除現(xiàn)有數(shù)據(jù)庫(kù)，從備份中重建數(shù)據(jù)庫(kù)-create database d6 -move to將數(shù)據(jù)庫(kù)文件移動(dòng)到新位置on primary(name=d6_data,filename='E:Program FilesMicrosoft SQL ServerMSSQLdatad6_Data.MDF',size=2MB)log on(name=d6_log,filename='E:Program FilesMicrosoft SQL ServerMSSQLdatad6_log.ldf',size=2MB)gobackupdatabase d6 to bak6 with initdro