SQL SERVER BACKUP DATABASE 加快備份速度方法（壓縮-Compression）

很多SQL SERVER DBA 在備份的時候，一般的撰寫格式，都是如下

BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH FORMAT,CHECKSUM;

對於比較大的資料庫，備份時間比較長，

最近在研究了BACKUP DATABASE 參數後，發現可以通過設定一下參數，加快備份速度：

1.備份的時候，設定多個備份組，比如
TO DISK = N'd:/1.bak',DISK = N'd:/2.bak',DISK = N'd:/3.bak',DISK = N'd:/4.bak'

2.使用BUFFERCOUNT 和MAXTRANSFERSIZE 選項。

BUFFERCOUNT   ：指定用於備份操作的 I/O 緩衝區總數。可以指定任何正整數；但是，較大的緩衝區數可能導致由於 Sqlservr.exe 進程中的虛擬位址空間不足而發生“記憶體不足”錯誤。

緩衝區使用的總計空間由下面公式確定：buffercount*maxtransfersize。

MAXTRANSFERSIZE   ：指定要在 SQL Server 和備份介質之間使用的傳輸單元最大值（位元組）。可能的值是 65536 位元組 (64 KB) 的倍數，最多可到 4194304 位元組 (4 MB)。

3.啟用備份壓縮選項（COMPRESSION）

該選項只在SQL Server 2008 Enterprise版本和SQL Server 2008 R2 Standard 以及所有更高版本都支援。

注意：壓縮選項會需要額外的CPU資源，因此建議不要在系統繁忙時候使用。

4。做為DBA，你可能需要保證資料的安全，一般會加上校正和參數（CHECKSUM），保證資料安全。

測試比較：

資料庫大小：53282.19 MB（包含記錄檔）

資料檔案大小是：32GB左右

使用標準備份方法：

BACKUP DATABASE [test]
TO DISK = N'd:/1.bak' WITH CHECKSUM;

需要用時04:08

使用最佳化參數的備份命令

BACKUP DATABASE [test]
TO DISK = N'D:\test_FULL_20110311_050001_1.bak',
DISK = N'D:\test_FULL_20110311_050001_2.bak',
DISK = N'D:\test_FULL_20110311_050001_3.bak',
DISK = N'D:\test_FULL_20110311_050001_4.bak'
WITH CHECKSUM, COMPRESSION, BUFFERCOUNT = 50, MAXTRANSFERSIZE = 4194304

備份用時：110.350 秒 

速度提高了50％

如果不使用CHECKSUM參數，備份速度還可以更快，但是做為DBA，資料的安全是第一位的，因此推薦加上CHECKSUM參數。

以上提起的參數只適用於SQL SERVER 2005和SQL SERVER 2008

 

RESTORE DATABASE  語句也可以使用

BUFFERCOUNT和MAXTRANSFERSIZE 參數