SQL備份 與還原

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

2、而只記錄*作的最終結(jié)果，因此占用日志空間小。 （4）只支持將數(shù)據(jù)庫還原到事務(wù)日志備份的時(shí)刻，而不支持即時(shí)點(diǎn)恢復(fù)，因此可能產(chǎn)生數(shù)據(jù)丟失。 - （3）簡單恢復(fù)模型 - （1）備份時(shí)只備份數(shù)據(jù)文件，還原時(shí)也用備份的數(shù)據(jù)文件恢復(fù)數(shù)據(jù)庫。 （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ù)庫恢復(fù)模型 alter database d1 set recovery bulk_logged alter database d1 set recovery ful

3、l - 2、備份設(shè)備 - 1）物理設(shè)備 - 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_devicebak

4、3.bak' - exec sp_addumpdevice '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ù)庫備份到備份設(shè)備 backup database d4 to bak4 -

5、restore headeronly from bak2 -查看備份設(shè)備中的內(nèi)容 - backup database d3 to disk='e:back_filed3.bak' -將數(shù)據(jù)庫備份到臨時(shí)備份文件 backup database d4 to disk='e:back_filed4.bak' - restore database d3 from bak3 -從備份設(shè)備還原數(shù)據(jù)庫 restore database d4 from disk='e:back_filed4.bak' -從備份文件還原數(shù)據(jù)庫 - 3、使用多個(gè)備份文件存儲(chǔ)備份 -

6、 1）SQL可同時(shí)向多個(gè)備份文件進(jìn)行寫*作。如果把這些文件放到多個(gè)磁帶機(jī)或磁盤中，則可提高備份速度。 2）這多個(gè)備份文件必須用同業(yè)型的媒體，并放到一個(gè)媒體集中。 3）媒體集中的文件必須同時(shí)使用，而不能單獨(dú)使用。 4）可以通過format命令將媒體集重新劃分，但原備份集中的數(shù)據(jù)不能再使用。 - backup database d4 to bak4,bak5,bak6 with medianame='bak456',format -備份D4并形成Media Set backup database d3 to bak4 -失敗，因Media set中文件必須同時(shí)使用 backup d

7、atabase d3 to bak4,bak5,bak6 -成功，將D3也備份到Media Set中 restore headeronly from bak4,bak5,bak6-查看Media Set中的備份內(nèi)容 - backup database d4 to bak4 with medianame='bak4',format -重新劃分Media Set backup database d3 to bak5,bak6 with medianame='bak56',format - backup database d1 to bak1 with init -w

8、ith init重寫備份設(shè)備中內(nèi)容 backup database d2 to bak1 with noinit -with noinit將內(nèi)容追加到備份設(shè)備中 restore headeronly from bak1 - 1）完全備份 - （1）是備份的基準(zhǔn)。在做備份時(shí)第一次備份都建議使用完全備份。 （2）完全備份會(huì)備份數(shù)據(jù)庫的所有數(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 no

9、init - 2）差異備份 - （1）基于完全備份。 （2）備份自最近一次完全備份以來的所有數(shù)據(jù)庫改變。 （3）恢復(fù)時(shí)，只應(yīng)用最近一次完全備份和最新的差異備份。 - 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 value

10、s(1,'a') backup database d2 to bak2 with differential,name='d2_diff2' 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 head

11、eronly from bak2 - 3）事務(wù)日志備份 - （1）基于完全備份。 （2）為遞增備份，即備份從上一次備份以來到備份時(shí)所寫的事務(wù)日志。 （3）允許恢復(fù)到故障時(shí)刻或者一個(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 with insert b1 v

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

13、b1 values(1,'a') backup log d4 to bak4 with insert b1 values(2,'b') backup database d4 to bak4 with differential,name='d4_diff1' insert b1 values(3,'c') backup log d4 to bak4 with insert b1 values(4,'d') backup log d4 to bak4 with insert b1 values(5,'d'

14、;) backup database d4 to bak4 with differential,name='d4_diff2' restore headeronly from bak4 - 日志清除 - 1）如果日志空間被填滿，數(shù)據(jù)庫將不能記錄修改。 2）數(shù)據(jù)庫在做完全備份時(shí)日志被截?cái)唷?3）如果將'Trans log on checkpoint'選項(xiàng)設(shè)為TRUE，則結(jié)果為不保存日志，即沒有日志記錄，不建議使用。 4）with truncate_only和with no_log設(shè)置日志滿時(shí)清除日志 5）with no_truncate則可以完整保存日志，不清除，

15、即使在數(shù)據(jù)文件已經(jīng)損壞情況下。主要用于數(shù)據(jù)庫出問題后在恢復(fù)前使用。可以將數(shù)據(jù)還原到出故障的那一時(shí)刻。 - exec sp_dboption d3 exec sp_dboption sp_dboption 'd3','trunc. log on chkpt.','true' -設(shè)置自動(dòng)清除數(shù)據(jù)庫日志 sp_dboption 'd3','trunc. log on chkpt.','false' -將自動(dòng)清除數(shù)據(jù)庫日志的選項(xiàng)去除 - backup log d4 with truncate_only -設(shè)

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

17、/文件組備份和日志備份進(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ù)庫時(shí)創(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

18、_log1.ldf', size=2Mb) use d5 go alter database d5 add file (name=d5_data3, filename='e:datad5d5_data5.ndf', size=2MB) to filegroup FG2 -將d5_data3加到文件組FG2中 alter database d5 add filegroup FG3 -增加文件組FG3 alter database d5 -將d5_data4加到文件組FG2中 add file (name=d5_data4, filename='e:datad5d5

19、_data4.ndf', size=2MB) to filegroup FG3 sp_helpdb d5 create table t1(c1 int not null,c2 char(10) not null) on primary -將不同表放到不同filegroup中 create table t2(c1 int not null,c2 char(10) not null) on FG2 create table t3(c1 int not null,c2 char(10) not null) on FG3 - backup database d5 to bak5 with in

20、it,name='d5_full' -filegroup備份 backup database d5 filegroup='primary' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG2' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG3' to bak5 with backup log d5 to bak5 with - backup

21、 database d5 to bak6 with init,name='d5_full' -file備份 backup database d5 file='d5_data1' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data2' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data3' to bak6 with backup log d5 to

22、 bak6 with backup database d5 file='d5_data4' to bak6 with backup log d5 to bak6 with restore headeronly from bak6 SQL還原 = 1、驗(yàn)證備份 - restore headeronly from bak3 restore filelistonly from bak3 with file=1 restore labelonly from bak3 restore verifyonly from bak3 - 2、從備份中還原 - restore headeronly

23、 from bak1 restore 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 - restore headeronly from bak3 -從日志備份中恢復(fù) restore database d3 from bak3 with file=1,norecover

24、y restore log d3 from bak3 with file=2,norecovery restore log d3 from bak3 with file=3,norecovery restore log d3 from bak3 with file=4,norecovery restore log d3 from bak3 with file=5,recovery - restore database d3 from bak3 with file=1,norecovery -恢復(fù)到指定時(shí)間 restore log d3 from bak3 with file=2,norecov

25、ery restore log d3 from bak3 with file=3,norecovery restore 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 -還原文件組備份 restore log d5 from bak5 with file=5,norecovery restore log d5 from bak5

26、with file=7,recovery - restore headeronly from bak6 -還原文件備份 restore database d5 file='d5_data3' from bak6 with file=6,norecovery restore log d5 from bak6 with file=7,norecovery restore log d5 from bak6 with file=9,recovery - restore database d5 from bak6 with replace -刪除現(xiàn)有數(shù)據(jù)庫，從備份中重建數(shù)據(jù)庫 - create database d6 -move to將數(shù)據(jù)庫文件移動(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