SQL備份語句

2019-11-03 08:35:25

字體：大中小

來源：轉載

供稿：網友

SQL備份
======================================================================
----------------------------------------------------------------------
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_device/bak2.bak' --創建永久磁盤備份設備
exec sp_addumpdevice 'disk','bak3','e:/back_device/bak3.bak'
----------------------------------------------------------------------
exec sp_addumpdevice 'disk','bak4','//sv2/backup/bak4.bak' --創建網絡永久磁盤備份設備
exec sp_addumpdevice 'disk','bak5','//sv2/backup/bak5.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_file/d3.bak'    --將數據庫備份到臨時備份文件
backup database d4 to disk='e:/back_file/d4.bak'
----------------------------------------------------------------------
restore database d3 from bak3           --從備份設備還原數據庫
restore database d4 from disk='e:/back_file/d4.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

上一篇：在SQL Server 2008中安裝安全審計

下一篇：五個SQL Server數據庫備份與恢復必需知道并且會用的具體操作