iT邦幫忙

DAY 17
11

SQL Server 無敵手冊系列 第 17

SQL Server 無敵手冊第十七篇-使用BACKUP(Transact-SQL)陳述式

閱讀前面幾篇所說明的自動化管理作業,您會發現在管理作業中您通常需要用到Transact-SQL來完成,所以接下來這篇我們就幫大家介紹管理工作中最常用到的BACKUP(Transact-SQL)陳述式。
當我們使用SQL Server Agent 進行排程備份時,通常我們必須選擇使用Transact-SQL指令碼的類型,並使用BACKUP陳述式進行,詳細說明請參考SQL Server 無敵手冊第十二篇

[BACKUP DATABASE陳述式]
您可以透過BACKUP DATABASE陳述式進行資料庫的完整備份、差異備份,以及針對某個檔案或檔案群組進行備份工作。這篇文章BACKUP DATABASE語法就針對資料庫備份來作介紹,語法簡述如下:

BACKUP DATABASE <資料庫名稱> TO <備份位置> WITH <備份選項>

上述語法是將資料庫依備份選項設定備份到指定的備份位置。
<備份位置>
除了備份裝置名稱外還可以帶入DISK(檔案名稱)與TAPE(磁帶機名稱)參數,另外TO之後也可以接上MIRROR TO將資料庫備份到次要備份裝置,MIRROR TO 子句和 TO 子句必須指定相同類型和數量的備份裝置。最大 MIRROR TO 子句數目是 3。舉例來說,將AdventureWorksDW這個資料庫備份到'D:\AdventureWorksDW.bak'這個檔案的語法如下:

BACKUP DATABASE [AdventureWorksDW] TO  DISK = N'D:\AdventureWorksDW.bak' 

如果您要被份到備份裝置(Backup Device),假設備份裝置名稱為'AWWD_Backup_Device',語法如下:

BACKUP DATABASE [AdventureWorksDW] TO  [AWWD_Backup_Device] 

關如何使用SQL Server 備份裝置,您可以參考SQL Server 無敵手冊第八篇
<備份選項>
備份選項說明如下:
DIFFERENTIAL:
進行差異備份。(沒有設定此參數時,BACKUP DATABASE 會建立完整備份。)
COPY_ONLY:
指定備份為「僅複製備份」(Copy-Only Backup),這不會影響正常的備份順序。此備份的建立與定期排程的日常備份無關。COPY_ONLY備份並不會影響資料庫的整體備份和還原程序,是在不截斷交易紀錄的情況下進行備份動作。
COMPRESSION | NO_COMPRESSION :
指定是否要在此備份上執行備份壓縮,以覆寫伺服器層級的預設值。
DESCRIPTION = { 'text' | @text_variable } :
設定此備份的說明文字,最多可以輸入255個字元。
NAME = { backup_set_name | @backup_set_var } :
指定備份組的名稱。名稱最多可有 128 個字元。如果未指定 NAME,它就是空白。
PASSWORD = { password | @password_variable }:
設定備份組的密碼。
EXPIREDATE = 'date':
設定到期時間,到期後可以覆寫這個備份的備份組。
RETAINDAYS = days:
設定保留天數,保留天數到後可以覆寫這個備份的備份組。如果同時指定RETAINDAYS和EXPIREDATE會以RETAINDAYS為優先。
NOINIT | INIT:
INIT會將備份作業覆寫,NOINIT則附加至備份媒體上的現有備份組。預設是附加至媒體上的最新備份組 (NOINIT)。
NOSKIP | SKIP:
NOSKIP先檢查媒體中所有備份組的到期日,才允許覆寫它們。SKIP則略過檢查動作,與設為NOSKIP。
NOFORMAT | FORMAT:
FORMAT對媒體進行格式化動作,寫入新的媒體標頭,這會讓現有內容會變成無效,與設為NOFORMAT。
MEDIADESCRIPTION = { text | @text_variable } :
輸入媒體集的文字描述,最多 255 個字元。
MEDIANAME = { media_name | @media_name_variable } :
設定整個備份媒體集的媒體名稱。媒體名稱不能超出 128 個字元。
MEDIAPASSWORD = { mediapassword | @mediapassword_variable }:
設定媒體集的密碼。
BLOCKSIZE = { blocksize | @blocksize_variable }:
指定實體區塊大小 (以位元組(bytes)為單位)。支援的大小為 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 位元組。磁帶裝置的預設值為 65536,其他裝置則為 512。此選項如果沒有設定,SQL Server 會自動選取裝置適用的區塊大小。
NO_CHECKSUM | CHECKSUM:
NO_CHECKSUM停用產生備份總和檢查碼 ,CHECKSUM啟用備份總和檢查碼,預設為NO_CHECKSUM。
STOP_ON_ERROR | CONTINUE_AFTER_ERROR:
STOP_ON_ERROR 總和檢查碼未驗證時便失敗,失敗後停止備份,CONTINUE_AFTER_ERROR則失敗後繼續執行備份工作,預設為CONTINUE_AFTER_ERROR。
STATS [ = percentage ] :
完成多少百分比時,回報一則訊息。
REWIND | NOREWIND:
REWIND 將釋放和倒轉磁帶,NOREWIND則 SQL Server 的執行個體會保有磁帶機的擁有權,直到在相同處理序中執行的 BACKUP 或 RESTORE 命令使用 REWIND 或 UNLOAD 選項,或是伺服器執行個體關閉為止。預設為REWIND。
UNLOAD | NOUNLOAD :
UNLOAD在備份完成之後,便自動倒轉和卸載磁帶,NOUNLOAD 仍會在磁帶機上保持載入,預設值為UNLOAD。

以下範例是對AdventureWorksDW進行差異備份,並指定備份到AWWD_Backup_Device這個備份裝置。

BACKUP DATABASE [AdventureWorksDW] TO  [AWWD_Backup_Device] WITH DIFFERENTIAL

介紹完BACKUP DATABASE後接著我們來說明BACKUP LOG,BACKUP LOG 是使用在交易紀錄備份上的陳述式,語法簡述如下:

BACKUP LOG <資料庫名稱> TO <備份位置> WITH <備份選項>

<備份選項>除了DIFFERENTIAL 不能搭配使用外,其他與BACKUP DATABASE相同,並且還多了下列選項:
NORECOVERY | STANDBY = undo_file_name :
NORECOVERY會備份記錄的結尾,並將資料庫保留在 RESTORING 狀態。當進行容錯移轉,將工作交給次要資料庫時,或在 RESTORE 作業之前儲存記錄結尾時,NORECOVERY 非常有用。STANDBY則須指定待命檔案名稱,使用STANDBY會備份記錄的結尾,並將資料庫保留在唯讀和 STANDBY 狀態。
NO_TRUNCATE:
指定不截斷記錄,且使 Database Engine 不論資料庫狀態為何,都一律嘗試進行備份。因此,利用 NO_TRUNCATE 取得的備份可能會有不完整的中繼資料。在資料庫已損毀的情況下,您可以利用這個選項來進行記錄的備份。

特別要跟大家說明的,在SQL Server 2000之前的版本,您可以使用BACKUP LOG 來截斷紀錄檔內已結束的交易紀錄,用來釋放使用空間。但是注意,SQL Server 2005 之後,必須將復原模式設定為[簡單]來清空交易紀錄。

當然講到這邊如果沒有拿出一點特別的撇步,就不配稱作無敵手冊了,如果您覺得上列的說明一點都記不住也沒有關係,您可以像下圖先在SQL Server Management Studio中設定完成。

最後如下圖所示,再選取指令碼目的地,即可產生對應的 Transact-SQL BACKUP 指令碼。

當然果如果只有這樣絕對還不夠稱為無敵手冊,一定要連如何將在SQL Server Management Studio的選項對應到陳述式都要完整說明,下圖將備份資料庫視窗中的[一般]頁面對應到BACKUP陳述式:

下圖將被份資料庫視窗中的[選項]頁面對應到BACKUP陳述式:

現在您已經學會如何使用BACKUP陳述式進行資料庫備份,搭配SQL Server Agent您可以更進一步掌握自動化管理工作。


上一篇
SQL Server 無敵手冊第十六篇-使用SQL Server 資料庫維護計畫
下一篇
SQL Server 無敵手冊第十八篇-使用RESTORE(Transact-SQL)陳述式
系列文
SQL Server 無敵手冊30

1 則留言

0
funkent
iT邦高手 1 級 ‧ 2009-11-09 22:22:46

補上SQL Server 無敵手冊全系列的連結
SQL Server 無敵手冊

我要留言

立即登入留言