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é)（如select into、create index等），而只記

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)境下選用。-alter database d1 set recovery simple -設(shè)置數(shù)據(jù)庫恢復(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)建。臨時備份文件：用于一次性備份，在備份時創(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ù)庫備份到備份設(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ù)庫備份到臨時備份文件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、使用多個備份文件存儲備份-1）SQL可同時向多個備份文件進(jìn)行寫*作。如果把這些文件放到多個磁帶機(jī)或磁盤中，則可提高備份速度

6、。2）這多個備份文件必須用同業(yè)型的媒體，并放到一個媒體集中。3）媒體集中的文件必須同時使用，而不能單獨使用。4）可以通過format命令將媒體集重新劃分，但原備份集中的數(shù)據(jù)不能再使用。-backup database d4 to bak4,bak5,bak6 with medianame='bak456',format -備份D4并形成Media Setbackup database d3 to bak4 -失敗，因Media set中文件必須同時使用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)。在做備份時第一次備份都建議使用完全備份。（2）完全備份會備份數(shù)據(jù)庫的所有數(shù)據(jù)文件、數(shù)據(jù)對象和數(shù)據(jù)。（3）會備份事務(wù)日志中任何未提交的事務(wù)。因為已提交的事務(wù)已經(jīng)寫入數(shù)據(jù)文件中。-backup database d1 to bak1 with init -完全備份backup database d1 to bak1 with noinit-2）差異備份-（1）基于完全備份。（2）備份自最近一次完全備份以來的所有數(shù)據(jù)庫改變。（3）恢復(fù)時，只應(yīng)用最近一次完全備份

9、和最新的差異備份。-backup database d2 to bak2 with init,name='d2_full' -差異備份，第一次備份時應(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）為遞增備份，即備份從上一次備份以來到備份時所寫的事務(wù)日志。（3）允許恢復(fù)到故障時刻或者

11、一個強(qiáng)制時間點。（4）恢復(fù)時，需要應(yīng)用完全備份和完全備份后的每次日志備份。-backup database d3 to bak3 with init,name='d3_full' -日志備份，第一次備份時應(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ù)庫將不能記錄修改。2）數(shù)據(jù)庫在做完全備份時日志被截斷。3）如果將'Trans log on checkpoint'選項設(shè)為TRUE，則結(jié)果為不保存日志，即沒有日志記錄，不建議使用。4）with truncate_only和with no_log設(shè)置日志滿時清除日志5）with no_truncate則可以完整保存日志，不清除，即使在數(shù)據(jù)文件已經(jīng)損壞情況下。主要用于數(shù)據(jù)庫出問題后在恢復(fù)前使用?？梢詫?shù)據(jù)還原到出故障的那一時刻。-exec sp_dboption d3exec sp_dboptionsp_dboption 'd3','trunc.

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

16、MO)-使用no_truncate完全+修改1+差異+修改2+差異+修改3+停止SQL，刪除數(shù)據(jù)庫數(shù)據(jù)文件+重啟SQLbackup log no_truncate再還原，可還原到修改3-4）文件/文件組備份-（1）用于超大型數(shù)據(jù)庫。（2）只備份選定的文件或者文件組。（3）必須同時作日志備份。（4）還原時用文件/文件組備份和日志備份進(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ù)庫時

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、驗證備份-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ù)到指定時間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ù)庫，從備份中重建數(shù)據(jù)庫-create database d6 -move to將數(shù)據(jù)庫文件移動到新位置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'

27、,size=2MB)gobackupdatabase d6 to bak6 with initdrop database d6restore database d6 from bak6with move 'd6_data' to 'e:datad6d6_data.mdf',move 'd6_log'to 'e:datad6d6_log.ldf'sp_helpdb d6-3、分離與重連接數(shù)據(jù)庫-sp_detach_db 'd6' sp_attach_db 'd6','e:datad6d6_da

28、ta.mdf','e:datad6d6_log.ldf'-sp_detach_db d6gocreate database d6on primary(filename='e:datad6d6_data.mdf')for attachgo-4、恢復(fù)損壞的系統(tǒng)數(shù)據(jù)庫-1）先備份MASTER、MSDB2）停止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目錄下的Re

29、buildm.exe重建master數(shù)據(jù)庫。（3）創(chuàng)建備份設(shè)備，指向以前的備份設(shè)備。（4）以單用戶模式啟動SQLcd programe filesmicrosoft sql servermssqlbinnsqlservr.exe -c -m（5）進(jìn)查詢分析器，從備份中恢復(fù)master數(shù)據(jù)庫。restore database master from masterbakrestore database msdb from disk='e:bakmsdb.bak'MASTER還原后，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è)備。在SQL Server 中使用sp_addumpdevice語句創(chuàng)建備份設(shè)備，其語法形式如下：sp_addumpdevice 磁盤類型,備份設(shè)備的邏輯名稱,備份設(shè)備的物理名稱例：創(chuàng)建備份設(shè)備zwbackuse mas