MS SQL Server 備份

資料庫的備份和恢複
一、備份資料庫

    1、開啟SQL企業管理器，在控制台根目錄中依次點開Microsoft SQL Server
    2、SQL Server組-->雙擊開啟你的伺服器-->雙擊開啟資料庫目錄
    3、選擇你的資料庫名稱（如論壇資料庫Forum）-->然後點上面菜單中的工具-->選擇備份資料庫
    4、備份選項選擇完全備份，目的中的備份到如果原來有路徑和名稱則選中名稱點刪除，然後點添加，如果原來沒有路徑和名稱則直接選擇添加，接著指定路徑和檔案名稱，指定後點確定返回備份視窗，接著點確定進行備份

二、還原資料庫

    1、開啟SQL企業管理器，在控制台根目錄中依次點開Microsoft SQL Server
    2、SQL Server組-->雙擊開啟你的伺服器-->點表徵圖欄的建立資料庫表徵圖，建立資料庫的名字自行取
    3、點擊建立好的資料庫名稱-->然後點上面菜單中的工具-->選擇恢複資料庫
    4、在彈出來的視窗中的還原選項中選擇從裝置-->點選擇裝置-->點添加-->然後選擇你的備份檔案名-->添加後點確定返回，這時候裝置欄應該出現您剛才選擇的Database Backup檔案名稱，備份號預設為1（如果您對同一個檔案做過多次備份，可以點擊備份號旁邊的查看內容，在複選框中選擇最新的一次備份後點確定）-->然後點擊上方常規旁邊的選項按鈕
    5、在出現的視窗中選擇在現有資料庫上強制還原，以及在恢複完成狀態中選擇使資料庫可以繼續運行但無法還原其它交易記錄的選項。在視窗的中間部位的將資料庫檔案還原為這裡要按照你SQL的安裝進行設定（也可以指定自己的目錄），邏輯檔案名稱不需要改動，移至物理檔案名稱要根據你所恢複的機器情況做改動，如您的SQL資料庫裝在D:"Program Files"Microsoft SQL Server"MSSQL"Data，那麼就按照您恢複機器的目錄進行相關改動改動，並且最後的檔案名稱最好改成您當前的資料庫名（如原來是zw0001.mdf，現在的資料庫是zw0002，就改成zw0002.mdf），日誌和資料檔案都要按照這樣的方式做相關的改動（日誌的檔案名稱是.ldf結尾的），這裡的恢複目錄您可以自由設定，前提是該目錄必須存在（如您可以指定d:"sqldata"zw0002.mdf或者d:"sqldata"zw0002.ldf），否則恢複將報錯
    6、修改完成後，點擊下面的確定進行恢複，這時會出現一個進度條，提示恢複的進度，恢複完成後系統會自動提示成功，如中間提示報錯，請記錄下相關的錯誤內容並詢問對SQL操作比較熟悉的人員，一般的錯誤無非是目錄錯誤或者檔案名稱重複或者檔案名稱錯誤或者空間不夠或者資料庫正在使用中的錯誤，資料庫正在使用的錯誤您可以嘗試關閉所有關於SQL視窗然後重新開啟進行恢複操作，如果還提示正在使用的錯誤可以將SQL服務停止然後重起看看，至於上述其它的錯誤一般都能按照錯誤內容做相應改動後即可恢複

三、設定每日自動備份資料庫

    1、開啟企業管理器，在控制台根目錄中依次點開Microsoft SQL Server-->SQL Server組-->雙擊開啟你的伺服器
    2、然後點上面菜單中的工具-->選擇資料庫維護計劃器
    3、下一步選擇要進行自動備份的資料-->下一步更新資料最佳化資訊，這裡一般不用做選擇-->下一步檢查資料完整性，也一般不選擇
    4、下一步指定資料庫維護計劃，預設的是1周備份一次，點擊更改選擇每天備份後點確定
    5、下一步指定備份的磁碟目錄，選擇指定目錄，如您可以在D盤建立一個目錄如：d:"databak，然後在這裡選擇使用此目錄，如果您的資料庫比較多最好選擇為每個資料庫建立子目錄，然後選擇刪除早於多少天前的備份，一般設定4－7天，這看您的具體備份要求，備份檔案副檔名一般都是bak就用預設的
    6、下一步指定交易記錄備份計劃，看您的需要做選擇-->下一步要產生的報表，一般不做選擇-->下一步維護計劃記錄，最好用預設的選項-->下一步完成
    7、完成後系統很可能會提示Sql Server Agent服務未啟動，先點確定完成計劃設定，然後找到案頭最右邊狀態列中的SQL綠色表徵圖，雙擊點開，在服務中選擇Sql Server Agent，然後點擊運行箭頭，選上下方的當啟動OS時自動啟動服務
    8、這個時候資料庫計劃已經成功的運行了，他將按照您上面的設定進行自動備份

轉自：http://blog.csdn.net/hzyishion/archive/2007/02/10/1507031.aspx

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 --將Database Backup到備份裝置
backup database d4 to bak4
------------------------------------------------------------------------------------------------
restore headeronly from bak2 --查看備份裝置中的內容
------------------------------------------------------------------------------------------------
backup database d3 to disk='e:"back_file"d3.bak' --將Database Backup到臨機操作備份檔案
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 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）如果日誌空間被填滿，資料庫將不能記錄修改。
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
-----------------------------------------------------------------------------------------------
4）檔案/檔案組備份
------------------------------------------------------------------
（1）用於超大型資料庫。
（2）只備份選定的檔案或者檔案組。
（3）必須同時作記錄備份。
（4）還原時用檔案/檔案組備份和記錄備份進行還原。
（5）備份量少，恢複速度快。
------------------------------------------------------------------
create database d5
on primary
(name=d5_data1,
filename='e:"data"d5"d5_data1.mdf',
size=2MB),
filegroup FG2 --建立資料庫時建立filegroup FG2
(name=d5_data2,
filename='e:"data"d5"d5_data2.ndf', --並將檔案d5_data2放到FG2中
size=2Mb)
log on
(name=d5_log1,
filename='e:"data"d5"d5_log1.ldf',
size=2Mb)

use d5
go
alter database d5
add file
(name=d5_data3,
filename='e:"data"d5"d5_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:"data"d5"d5_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 --刪除現有資料庫，從備份中重建資料庫
-----------------------------------------------------------------------------------------------
create database d6 --move to將資料庫檔案移動到新位置
on primary
(name=d6_data,
filename='E:"Program Files"Microsoft SQL Server"MSSQL"data"d6_Data.MDF',
size=2MB)
log on
(name=d6_log,
filename='E:"Program Files"Microsoft SQL Server"MSSQL"data"d6_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:"data"d6"d6_data.mdf',
move 'd6_log' to 'e:"data"d6"d6_log.ldf'

sp_helpdb d6
-----------------------------------------------------------------------------------------------
3、分離與重串連資料庫
--------------------------------------
sp_detach_db 'd6'
sp_attach_db 'd6','e:"data"d6"d6_data.mdf','e:"data"d6"d6_log.ldf'
--------------------------------------
sp_detach_db d6
go
create database d6
on primary
(filename='e:"data"d6"d6_data.mdf')
for attach
go
-----------------------------------------------------------------------------------------------
4、恢複損壞的系統資料庫
-----------------------------------------------------------------------------------------------
1）先備份MASTER、MSDB
2）停止SQL服務，將MASTER資料庫檔案刪除或者重新命名。這樣，SQL服務將不能啟動。
3）系統資料庫的還原
-----------------------------------------------
（1）如果SQL服務還能啟動，則從備份中恢複系統資料庫。
（2）如果SQL服務不能啟動，則需要重建系統資料庫。
使用SQL檔案夾TOOLS"BINN目錄下的Rebuildm.exe重建master資料庫。
（3）建立備份裝置，指向以前的備份裝置。
（4）以單一使用者模式啟動SQL
cd programe files"microsoft sql server"mssql"binn
sqlservr.exe -c -m
（5）進查詢分析器，從備份中恢複master資料庫。
restore database master from masterbak
restore database msdb from disk='e:"bak"msdb.bak'
MASTER還原後，SQL中使用者資料庫的資訊也會恢複。
（6）如果MASTER沒有備份，則需要用sp_attach_db命令將使用者資料庫附加到新的MASTER資料庫中。
-----------------------------------------------------------------------------------------------
5、自動化備份實現（要將sqlserveragent服務設定為自動啟動，並啟動該服務）

轉自：http://www.80design.net/article.asp?id=489