MSSQL備份語句以及恢復介紹

----------------------------------------------------------------------


1、SQL資料庫恢復模型


----------------------------------------------------------------------


1）完全恢復模型


-----------------


（1）備份時要備份資料庫的資料檔案和日誌檔


（2）還原時使用資料庫的備份的資料檔案副本和全部日誌資訊來恢復資料庫。


（3）能還原全部資料，並可以將資料庫恢復到任意指定的時刻。


（4）為保證實現即時間點復原，對資料庫的所有*作都將完整地記入日誌，這樣，日誌佔用空間較大，對性能也有所影響。


------------------


（2）大容量日誌記錄恢復模型


------------------


（1）備份時要備份資料庫的資料檔案和日誌檔


（2）還原時使用資料庫的備份的資料檔案副本和全部日誌資訊來恢復資料庫。


（3）日誌中不記錄*作細節（如select into、create index等），而只記錄*作的最終結果，因此佔用日誌空間小。


（4）只支援將資料庫還原到事務記錄備份的時刻，而不支援即時間點復原，因此可能產生資料丟失。


-------------------


（3）簡單恢復模型


-------------------


（1）備份時只備份資料檔案，還原時也用備份的資料檔案恢復資料庫。


（2）只能將資料恢復到資料檔案備份的時刻，可能產生最多的資料丟失。


（3）不適于生產系統和大規模*作環境下選用。


-----------------------------------------


alter database d1 set recovery simple     --設置資料庫恢復模型


alter database d1 set recovery bulk_logged


alter database d1 set recovery full


----------------------------------------------------------------------


2、備份裝置


----------------------------------------------------------------------


1）物理設備


---------------------------


disk：支援本地磁片或者網路備份


tape：支援磁帶機備份


name pipe：支援協力廠商備份軟體


---------------------------


2）邏輯裝置


---------------------------


永久備份檔案：可以重複使用，應該在備份前創建。


臨時備份檔案：用於一次性備份，在備份時創建。


-------------------------------------------------


exec sp_addumpdevice 'disk','bak2','e:back_devicebak2.bak' --創建永久磁片備份裝置


exec sp_addumpdevice 'disk','bak3','e:back_devicebak3.bak'


----------------------------------------------------------------------


exec sp_addumpdevice 'disk','bak4','\sv2backupbak4.bak' --創建網路永久磁片備份裝置


exec sp_addumpdevice 'disk','bak5','\sv2backupbak5.bak'


----------------------------------------------------------------------


exec sp_dropdevice 'bak5'              --刪除備份裝置


----------------------------------------------------------------------


backup database d3 to bak3      --將資料庫備份到備份裝置


backup database d4 to bak4


----------------------------------------------------------------------


restore headeronly from bak2    --查看備份裝置中的內容


----------------------------------------------------------------------


backup database d3 to disk='e:back_filed3.bak'    --將資料庫備份到臨時備份檔案


backup database d4 to disk='e:back_filed4.bak'


----------------------------------------------------------------------


restore database d3 from bak3           --從備份裝置還原資料庫       


restore database d4 from disk='e:back_filed4.bak' --從備份檔案還原資料庫


----------------------------------------------------------------------


3、使用多個備份檔案存儲備份


----------------------------------------------------------------------


1）SQL可同時向多個備份檔案進行寫*作。 如果把這些檔放到多個磁帶機或磁片中，則可提高備份速度。


2）這多個備份檔案必須用同業型的媒體，並放到一個媒體集中。


3）媒體集中的檔必須同時使用，而不能單獨使用。


4）可以通過format命令將媒體集重新劃分，但原備份組中的資料不能再使用。


--------------------------------------------------------------------


backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --備份D4並形成Media Set


backup database d3 to bak4      --失敗，因Media set中檔必須同時使用


backup database d3 to bak4,bak5,bak6    --成功，將D3也備份到Media Set中


restore headeronly from bak4,bak5,bak6--查看Media Set中的備份內容


----------------------------------------------------------------------


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     --with init重寫備份裝置中內容


backup database d2 to bak1 with noinit --with noinit將內容追加到備份裝置中


restore headeronly from bak1


----------------------------------------------------------------------


4、備份的方法


----------------------------------------------------------------------


1）完全備份


-------------------------------------------


（1）是備份的基準。 在做備份時第一次備份都建議使用完全備份。


（2）完全備份會備份資料庫的所有資料檔案、資料物件和資料。


（3）會備份事務日誌中任何未提交的事務。 因為已提交的事務已經寫入資料檔案中。


--------------------------------------------


backup database d1 to bak1 with init     --完全備份


backup database d1 to bak1 with noinit


----------------------------------------------------------------------


2）差異備份


---------------------------------------------


（1）基於完全備份。


（2）備份自最近一次完全備份以來的所有資料庫改變。


（3）恢復時，只應用最近一次完全備份和最新的差異備份。


-----------------------------------------------


backup database d2 to bak2 with init,name='d2_full' --差異備份，第一次備份時應做完全備份


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_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 headeronly from bak2


----------------------------------------------------------------------


3）事務記錄備份


-------------------------------------------------------------


（1）基於完全備份。


（2）為遞增備份，即備份從上一次備份以來到備份時所寫的事務日誌。


（3）允許恢復到故障時刻或者一個強制時間點。


（4）恢復時，需要應用完全備份和完全備份後的每次記錄備份。


-------------------------------------------------------------


backup database d3 to bak3 with init,name='d3_full' --記錄備份，第一次備份時應做完全備份


create table b1(c1 int not null,c2 char(10) not null)


backup log d3 to bak3 with


insert b1 values(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 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')


backup database d4 to bak4 with differential,name='d4_diff2'


restore headeronly from bak4


----------------------------------------------------------------------


日誌清除


-----------------------------------------


1）如果日誌空間被填滿，資料庫將不能記錄修改。


2）資料庫在做完全備份時日誌被截斷。


3）如果將'Trans log on checkpoint'選項設為TRUE，則結果為不保存日誌，即沒有日誌記錄，不建議使用。


4）with truncate_only和with no_log設置日誌滿時清除日誌


5）with no_truncate則可以完整保存日誌，不清除，即使在資料檔案已經損壞情況下。 主要用於資料庫出問題後在恢復前使用。 可以將資料還原到出故障的那一時刻。


-------------------------------------------


exec sp_dboption d3


exec sp_dboption


sp_dboption 'd3','trunc. log on chkpt.','true'     --設置自動清除資料庫日誌


sp_dboption 'd3','trunc. log on chkpt.','false'    --將自動清除資料庫日誌的選項去除


----------------------------------------------------------------------


backup log d4 with truncate_only    --設置D4日誌滿時清除日誌，並做清除記錄


----------------------------------------------------------------------


backup log d4 with no_log       --設置D4日誌滿時清除日誌，但不做清除記錄


----------------------------------------------------------------------


backup log d4 to bak4 with no_truncate--在D4資料庫損壞時馬上備份當前資料庫日誌(DEMO)


--------


使用no_truncate


完全+修改1+差異+修改2+差異+修改3+停止SQL，刪除資料庫資料檔案+重啟SQL


backup log no_truncate


再還原，可還原到修改3