教你如何用SQL備份和還原數(shù)據(jù)庫
數(shù)據(jù)庫的備份和恢復(fù)
一、備份數(shù)據(jù)庫
1、打開SQL企業(yè)管理器，在控制臺根目錄中依次點開Microsoft SQL Server
2、SQL Server組-->雙擊打開你的服務(wù)器-->雙擊打開數(shù)據(jù)庫目錄
3、選擇你的數(shù)據(jù)庫名稱（如論壇數(shù)據(jù)庫Forum）-->然后點上面菜單中的工具-->選擇備份數(shù)據(jù)庫
4、備份選項選擇完全備份，目的中的備份到如果原來有路徑和名稱則選中名稱點刪除，然后點添加，如果原來沒有路徑和名稱則直接選擇添加，接著指定路徑和文件名，指定后點確定返回備份窗口，接著點確定進(jìn)行備份
二、還原數(shù)據(jù)庫
1、打開SQL企業(yè)管理器，在控制臺根目錄中依次點開Microsoft SQL Server
2、SQL Server組-->雙擊打開你的服務(wù)器-->點圖標(biāo)欄的新建數(shù)據(jù)庫圖標(biāo)，新建數(shù)據(jù)庫的名字自行取
3、點擊新建好的數(shù)據(jù)庫名稱-->然后點上面菜單中的工具-->選擇恢復(fù)數(shù)據(jù)庫
4、在彈出來的窗口中的還原選項中選擇從設(shè)備-->點選擇設(shè)備-->點添加-->然后選擇你的備份文件名-->添加后點確定返 回，這時候設(shè)備欄應(yīng)該出現(xiàn)您剛才選擇的數(shù)據(jù)庫備份文件名，備份號默認(rèn)為1（如果您對同一個文件做過多次備份，可以點擊備份號旁邊的查看內(nèi)容，在復(fù)選框中選 擇最新的一次備份后點確定）-->然后點擊上方常規(guī)旁邊的選項按鈕
5、在出現(xiàn)的窗口中選擇在現(xiàn)有數(shù)據(jù)庫上強(qiáng)制還原，以及在恢復(fù)完成狀態(tài)中選擇使數(shù)據(jù)庫可以繼續(xù)運行但無法還原其它事務(wù)日志的選項。在窗口的中間部位的將數(shù)據(jù) 庫文件還原為這里要按照你SQL的安裝進(jìn)行設(shè)置（也可以指定自己的目錄），邏輯文件名不需要改動，移至物理文件名要根據(jù)你所恢復(fù)的機(jī)器情況做改動，如您的 SQL數(shù)據(jù)庫裝在D:\Program Files\Microsoft SQL Server\MSSQL\Data，那么就按照您恢復(fù)機(jī)器的目錄進(jìn)行相關(guān)改動改動，并且最后的文件名最好改成您當(dāng)前的數(shù)據(jù)庫名（如原來是 zw0001.mdf，現(xiàn)在的數(shù)據(jù)庫是zw0002，就改成zw0002.mdf），日志和數(shù)據(jù)文件都要按照這樣的方式做相關(guān)的改動（日志的文件名 是.ldf結(jié)尾的），這里的恢復(fù)目錄您可以自由設(shè)置，前提是該目錄必須存在（如您可以指定d:\sqldata\zw0002.mdf或者d: \sqldata\zw0002.ldf），否則恢復(fù)將報錯
6、修改完成后，點擊下面的確定進(jìn)行恢復(fù)，這時會出現(xiàn)一個進(jìn)度條，提示恢復(fù)的進(jìn)度，恢復(fù)完成后系統(tǒng)會自動提示成功，如中間提示報錯，請記錄下相關(guān)的錯誤內(nèi) 容并詢問對SQL操作比較熟悉的人員，一般的錯誤無非是目錄錯誤或者文件名重復(fù)或者文件名錯誤或者空間不夠或者數(shù)據(jù)庫正在使用中的錯誤，數(shù)據(jù)庫正在使用的 錯誤您可以嘗試關(guān)閉所有關(guān)于SQL窗口然后重新打開進(jìn)行恢復(fù)操作，如果還提示正在使用的錯誤可以將SQL服務(wù)停止然后重起看看，至于上述其它的錯誤一般都 能按照錯誤內(nèi)容做相應(yīng)改動后即可恢復(fù)
三、設(shè)定每日自動備份數(shù)據(jù)庫
1、打開企業(yè)管理器，在控制臺根目錄中依次點開Microsoft SQL Server-->SQL Server組-->雙擊打開你的服務(wù)器
2、然后點上面菜單中的工具-->選擇數(shù)據(jù)庫維護(hù)計劃器
3、下一步選擇要進(jìn)行自動備份的數(shù)據(jù)-->下一步更新數(shù)據(jù)優(yōu)化信息，這里一般不用做選擇-->下一步檢查數(shù)據(jù)完整性，也一般不選擇
4、下一步指定數(shù)據(jù)庫維護(hù)計劃，默認(rèn)的是1周備份一次，點擊更改選擇每天備份后點確定
5、下一步指定備份的磁盤目錄，選擇指定目錄，如您可以在D盤新建一個目錄如：d:\databak，然后在這里選擇使用此目錄，如果您的數(shù)據(jù)庫比較多最 好選擇為每個數(shù)據(jù)庫建立子目錄，然后選擇刪除早于多少天前的備份，一般設(shè)定4－7天，這看您的具體備份要求，備份文件擴(kuò)展名一般都是bak就用默認(rèn)的
6、下一步指定事務(wù)日志備份計劃，看您的需要做選擇-->下一步要生成的報表，一般不做選擇-->下一步維護(hù)計劃歷史記錄，最好用默認(rèn)的選項-->下一步完成
7、完成后系統(tǒng)很可能會提示Sql Server Agent服務(wù)未啟動，先點確定完成計劃設(shè)定，然后找到桌面最右邊狀態(tài)欄中的SQL綠色圖標(biāo)，雙擊點開，在服務(wù)中選擇Sql Server Agent，然后點擊運行箭頭，選上下方的當(dāng)啟動OS時自動啟動服務(wù)
8、這個時候數(shù)據(jù)庫計劃已經(jīng)成功的運行了，他將按照您上面的設(shè)置進(jìn)行自動備份
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等），而只記錄*作的最終結(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_logged
alter database d1 set recovery full
----------------------------------------------------------------------------------
2、備份設(shè)備
----------------------------------------------------------------------------------
1）物理設(shè)備
---------------------------
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_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
------------------------------------------------------------------------------------------------
restore headeronly from bak2 --查看備份設(shè)備中的內(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è)備還原數(shù)據(jù)庫
restore database d4 from disk=e:back_filed4.bak --從備份文件還原數(shù)據(jù)庫
------------------------------------------------------------------------------------------------
3、使用多個備份文件存儲備份
----------------------------------------------------------------------
1）SQL可同時向多個備份文件進(jìn)行寫*作。如果把這些文件放到多個磁帶機(jī)或磁盤中，則可提高備份速度。
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重寫備份設(shè)備中內(nèi)容
backup database d2 to bak1 with noinit --with 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)用最近一次完全備份和最新的差異備份。
-----------------------------------------------
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_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）事務(wù)日志備份
-------------------------------------------------------------
（1）基于完全備份。
（2）為遞增備份，即備份從上一次備份以來到備份時所寫的事務(wù)日志。
（3）允許恢復(fù)到故障時刻或者一個強(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 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選項設(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 d3
exec sp_dboption
sp_dboption d3,trunc. 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ù)庫日志(DEMO)
--------
使用no_truncate
完全+修改1+差異+修改2+差異+修改3+停止SQL，刪除數(shù)據(jù)庫數(shù)據(jù)文件+重啟SQL
backup 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ù)庫時創(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 --從完全備份中恢復(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,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 --恢復(fù)到指定時間
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、恢復(fù)損壞的系統(tǒng)數(shù)據(jù)庫
-----------------------------------------------------------------------------------------------
1）先備份MASTER、MSDB
2）停止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目錄下的Rebuildm.exe重建master數(shù)據(jù)庫。
（3）創(chuàng)建備份設(shè)備，指向以前的備份設(shè)備。
（4）以單用戶模式啟動SQL
cd programe filesmicrosoft sql servermssqlbinn
sqlservr.exe -c -m
（5）進(jìn)查詢分析器，從備份中恢復(fù)master數(shù)據(jù)庫。
restore database master from masterbak
restore database msdb from disk=e:bakmsdb.bak
MASTER還原后，SQL中用戶數(shù)據(jù)庫的信息也會恢復(fù)。
（6）如果MASTER沒有備份，則需要用sp_attach_db命令將用戶數(shù)據(jù)庫附加到新的MASTER數(shù)據(jù)庫中。
-----------------------------------------------------------------------------------------------
5、自動化備份實現(xiàn)（要將sqlserveragent服務(wù)設(shè)置為自動啟動，并啟動該服務(wù)）
