===================================================================================
SQL備份
-----------------------------------------------------------------------------------
1、SQL數(shù)據(jù)庫恢復模型
-----------------------------------------------------------------------------------
1）完全恢復模型
-----------------
（1）備份時要備份數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件
（2）還原時使用數(shù)據(jù)庫的備份的數(shù)據(jù)文件副本和全部日志信息來恢復數(shù)據(jù)庫。
（3）能還原全部數(shù)據(jù)，并可以將數(shù)據(jù)庫恢復到任意指定的時刻。
（4）為保證實現(xiàn)即時點恢復，對數(shù)據(jù)庫的所有*作都將完整地記入日志，這樣，日志占用空間較大，對性能也有所影響。
------------------
（2）大容量日志記錄恢復模型
------------------
（1）備份時要備份數(shù)據(jù)庫的數(shù)據(jù)文件和日志文件
（2）還原時使用數(shù)據(jù)庫的備份的數(shù)據(jù)文件副本和全部日志信息來恢復數(shù)據(jù)庫。
（3）日志中不記錄*作細節(jié)（如select into、create index等），而只記錄*作的最終結果，因此占用日志空間小。
（4）只支持將數(shù)據(jù)庫還原到事務日志備份的時刻，而不支持即時點恢復，因此可能產(chǎn)生數(shù)據(jù)丟失。
-------------------
（3）簡單恢復模型
-------------------
（1）備份時只備份數(shù)據(jù)文件，還原時也用備份的數(shù)據(jù)文件恢復數(shù)據(jù)庫。
（2）只能將數(shù)據(jù)恢復到數(shù)據(jù)文件備份的時刻，可能產(chǎn)生最多的數(shù)據(jù)丟失。
（3）不適于生產(chǎn)系統(tǒng)和大規(guī)模*作環(huán)境下選用。
-----------------------------------------
alter database d1 set recovery simple --設置數(shù)據(jù)庫恢復模型
alter database d1 set recovery bulk_logged
alter database d1 set recovery full
----------------------------------------------------------------------------------
2、備份設備
----------------------------------------------------------------------------------
1）物理設備
---------------------------
disk：支持本地磁盤或者網(wǎng)絡備份
tape：支持磁帶機備份
name pipe：支持第三方備份軟件
---------------------------
2）邏輯設備
---------------------------
永久備份文件：可以重復使用，應該在備份前創(chuàng)建。
臨時備份文件：用于一次性備份，在備份時創(chuàng)建。
-------------------------------------------------
exec sp_addumpdevice disk,bak2,e:back_devicebak2.bak --創(chuàng)建永久磁盤備份設備
exec sp_addumpdevice disk,bak3,e:back_devicebak3.bak
------------------------------------------------------------------------------------------------
exec sp_addumpdevice disk,bak4,\sv2backupbak4.bak --創(chuàng)建網(wǎng)絡永久磁盤備份設備
exec sp_addumpdevice disk,bak5,\sv2backupbak5.bak
------------------------------------------------------------------------------------------------
exec sp_dropdevice bak5 --刪除備份設備
------------------------------------------------------------------------------------------------
backup database d3 to bak3 --將數(shù)據(jù)庫備份到備份設備
backup database d4 to bak4
------------------------------------------------------------------------------------------------
restore headeronly from bak2 --查看備份設備中的內(nèi)容
------------------------------------------------------------------------------------------------
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ù)據(jù)庫
restore database d4 from disk=e:back_filed4.bak --從備份文件還原數(shù)據(jù)庫
------------------------------------------------------------------------------------------------
3、使用多個備份文件存儲備份
----------------------------------------------------------------------
1）SQL可同時向多個備份文件進行寫*作。如果把這些文件放到多個磁帶機或磁盤中，則可提高備份速度。
2）這多個備份文件必須用同業(yè)型的媒體，并放到一個媒體集中。
3）媒體集中的文件必須同時使用，而不能單獨使用。
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中文件必須同時使用
backup database 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 --with init重寫備份設備中內(nèi)容
backup database d2 to bak1 with noinit --with noinit將內(nèi)容追加到備份設備中
restore headeronly from bak1
-----------------------------------------------------------------------------------------------
4、備份的方法
----------------------------------------------------------------------------------------------
1）完全備份
-------------------------------------------
（1）是備份的基準。在做備份時第一次備份都建議使用完全備份。
（2）完全備份會備份數(shù)據(jù)庫的所有數(shù)據(jù)文件、數(shù)據(jù)對象和數(shù)據(jù)。
（3）會備份事務日志中任何未提交的事務。因為已提交的事務已經(jīng)寫入數(shù)據(jù)文件中。
--------------------------------------------
backup database d1 to bak1 with init --完全備份
backup database d1 to bak1 with noinit
-----------------------------------------------------------------------------------------------
2）差異備份
---------------------------------------------
（1）基于完全備份。
（2）備份自最近一次完全備份以來的所有數(shù)據(jù)庫改變。
（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 name=d3_log1
insert b1 values(1,a)
backup log d3 to bak3 with name=d3_log2
insert b1 values(2,b)
backup log d3 to bak3 with name=d3_log3
insert b1 values(3,c)
backup log d3 to bak3 with name=d3_log4
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 name=d4_log1
insert b1 values(1,a)
backup log d4 to bak4 with name=d4_log2
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 name=d4_log3
insert b1 values(4,d)
backup log d4 to bak4 with name=d4_log4
insert b1 values(5,d)
backup database d4 to bak4 with differential,name=d4_diff2
restore headeronly from bak4
-----------------------------------------------------------------------------------------------
日志清除
-----------------------------------------
1）如果日志空間被填滿，數(shù)據(jù)庫將不能記錄修改。
2）數(shù)據(jù)庫在做完全備份時日志被截斷。
3）如果將Trans log on checkpoint選項設為TRUE，則結果為不保存日志，即沒有日志記錄，不建議使用。
4）with truncate_only和with no_log設置日志滿時清除日志
5）with no_truncate則可以完整保存日志，不清除，即使在數(shù)據(jù)文件已經(jīng)損壞情況下。主要用于數(shù)據(jù)庫出問題后在恢復前使用?？梢詫?shù)據(jù)還原到出故障的那一時刻。
-------------------------------------------
exec sp_dboption d3
exec sp_dboption
sp_dboption d3,trunc. log on chkpt.,true --設置自動清除數(shù)據(jù)庫日志
sp_dboption d3,trunc. log on chkpt.,false --將自動清除數(shù)據(jù)庫日志的選項去除
-----------------------------------------------------------------------------------------------
backup log d4 with truncate_only --設置D4日志滿時清除日志，并做清除記錄
-----------------------------------------------------------------------------------------------
backup log d4 with no_log --設置D4日志滿時清除日志，但不做清除記錄
-----------------------------------------------------------------------------------------------
backup log d4 to bak4 with no_truncate --在D4數(shù)據(jù)庫損壞時馬上備份當前數(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）必須同時作日志備份。
（4）還原時用文件/文件組備份和日志備份進行還原。
（5）備份量少，恢復速度快。
------------------------------------------------------------------
create database d5
on primary
(name=d5_data1,
filename=e:datad5d5_data1.mdf,
size=2MB),
filegroup FG2 --創(chuàng)建數(shù)據(jù)庫時創(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 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_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 init,name=d5_full --filegroup備份
backup database d5 filegroup=primary to bak5 with name=d5_primary
backup log d5 to bak5 with name=d5_log1
backup database d5 filegroup=FG2 to bak5 with name=d5_FG2
backup log d5 to bak5 with name=d5_log2
backup database d5 filegroup=FG3 to bak5 with name=d5_FG3
backup log d5 to bak5 with name=d5_log3
----------------------------------------------------------------------------------------------
backup database d5 to bak6 with init,name=d5_full --file備份
backup database d5 file=d5_data1 to bak6 with name=d5_data1
backup log d5 to bak6 with name=d5_log1
backup database d5 file=d5_data2 to bak6 with name=d5_data2
backup log d5 to bak6 with name=d5_log2
backup database d5 file=d5_data3 to bak6 with name=d5_data3
backup log d5 to bak6 with name=d5_log3
backup database d5 file=d5_data4 to bak6 with name=d5_data4
backup log d5 to bak6 with name=d5_log4
restore headeronly from bak6
===============================================================================================
SQL還原
===============================================================================================
1、驗證備份
------------------------------------------------------------
restore headeronly from bak3
restore filelistonly from bak3 with file=1
restore labelonly from bak3
restore verifyonly from bak3
-----------------------------------------------------------------------------------------------
2、從備份中還原
-----------------------------------------------------------------------------------------------
restore headeronly from bak1
restore database d1 from bak1 with file=2 --從完全備份中恢復
-----------------------------------------------------------------------------------------------
restore headeronly from bak2 --從差異備份中恢復
restore database d2 from bak2 with file=1,norecovery
restore database d2 from bak2 with file=5,recovery
-----------------------------------------------------------------------------------------------
restore headeronly from bak3 --從日志備份中恢復
restore database d3 from bak3 with file=1,norecovery
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 --恢復到指定時間
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,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 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ù)庫文件移動到新位置
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)
go
backup database d6 to bak6 with init
drop database d6
restore database d6 from bak6
with 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_data.mdf,e:datad6d6_log.ldf
--------------------------------------
sp_detach_db d6
go
create database d6
on primary
(filename=e:datad6d6_data.mdf)
for attach
go
-----------------------------------------------------------------------------------------------
4、恢復損壞的系統(tǒng)數(shù)據(jù)庫
-----------------------------------------------------------------------------------------------
1）先備份MASTER、MSDB
2）停止SQL服務，將MASTER數(shù)據(jù)庫文件刪除或者重命名。這樣，SQL服務將不能啟動。
3）系統(tǒng)數(shù)據(jù)庫的還原
-----------------------------------------------
（1）如果SQL服務還能啟動，則從備份中恢復系統(tǒng)數(shù)據(jù)庫。
（2）如果SQL服務不能啟動，則需要重建系統(tǒng)數(shù)據(jù)庫。
使用SQL文件夾TOOLSBINN目錄下的Rebuildm.exe重建master數(shù)據(jù)庫。
（3）創(chuàng)建備份設備，指向以前的備份設備。
（4）以單用戶模式啟動SQL
cd programe filesmicrosoft sql servermssqlbinn
sqlservr.exe -c -m
（5）進查詢分析器，從備份中恢復master數(shù)據(jù)庫。
restore database master from masterbak
restore database msdb from disk=e:bakmsdb.bak
MASTER還原后，SQL中用戶數(shù)據(jù)庫的信息也會恢復。
（6）如果MASTER沒有備份，則需要用sp_attach_db命令將用戶數(shù)據(jù)庫附加到新的MASTER數(shù)據(jù)庫中。
-----------------------------------------------------------------------------------------------
5、自動化備份實現(xiàn)（要將sqlserveragent服務設置為自動啟動，并啟動該服務）
