MISTECH 技術手抄本

MSSQL Server 2012 Transaction Concept 概念：

MSSQL Server 2012 在資料寫的的運作上，是秉持著 WAL(Write Ahead Logging) 的機制，Transaction 存取磁碟的作業，主要是依照下列 4 個步驟來作業：

• 1. 將 Transaction 寫入 LOG File
• 2. 將 Transaction 的異動修改 Buffer (記憶體) 裡的資料
• 3. 交易完成 Transaction Commit
• 4. 將 Buffer (記憶體) 裡的資料寫入 Disk ( Data Files )裡

Recovery Model：

在說明 MSSQL Server 2012 的備份模式前，要先瞭解它的 Recovery Model，MSSQL Server 2012 的 Recovery Model 有三種模式：

1. Simple：當 Transaction Commit 交易確認後，此 Transaction Log 就會從 LOG File 中清除(Truncate)，所以 LOG File 並不會長大，LOG File 的檔案大小永遠保持最小。

2. Full：所有的 Transaction 交易都會保留在 LOG File 中，所以 LOG File 會一直長大，除了作備份作業後，LOG File 裡的 Transaction 內容才會被清除(Truncate)，當然 LOG File 檔案要縮小，還得手動執行 Shrink Log 才行，如下圖示

3. Bulk Logged：當有 Bulk Insert 的 SQL 語法時，這些 批次 的 Transaction 不會記錄在 LOG File 中。

Backup Type：

在 MSSQL Server 2012 的備份模式有七種模式，

• 1. Full：所有的 Data files 及 Transaction Log 都備份
• 2. Differential：只備份差異部份
• 3. Partial：主要的 Primary Filegroup，與指定的 Filegroup
• 4. Transaction Log：只備份 Log File 中的 Transaction，回存時需搭配 Full 備份的檔案
• 5. Tail-log Backup：只備份尚存在 LOG File 中的 Transaction，如 15:00 執行了 Transaction Log 備份，下一次的備份時間在 16:00 ，但 SQL Server 在 15:30 掛了，此時尚存在 LOG File 中的 Transaction 就是 Tail-log。
• 6. File/File Group：指定的 File or File Group 備份
• 7. Copy Only：這個模式主要是將現行運行的資料庫線上烤貝 Copy 一份到異地端存放用的，這個作業並不會影響 LSN 的序列號。

備份實作：

一般簡單全備的的指令為

BACKUP DATABASE { database_name | @database_name_var }   TO <backup_device> [ ,...n ]   [ <MIRROR TO clause> ] [ next-mirror-to ]   [ WITH { DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;]

如：
BACKUP DATABASE Northwind
TO DISK ='C:\SQLDB_DATA\back\Northwind.bck'

 但這樣的作法，並不是很好的方式，建議應該建立一個 Backup Device 來作備份的目標物，在 Backup Device 再設定實際的磁碟或磁帶，這樣在未來儲存目標物 (destination) 有變更時，只要修改此 Backup Device 的儲存目標物 (destination) 即可。

建立備份裝置 mybackup(backup device)，指令如：

EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'mybackup', @physicalname = N'C:\backups\mybackup.bak'

也可以用 SQL Server Management Studio 來建立