文章出處

SQLSERVER數據庫備份操作和還原操作做了什么

自己也測試了一下，下面說的錯誤日志指的是SQLSERVER ERRORLOG

一般在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG路徑下，如果SQLSERVER安裝在C盤的話

要打開跟蹤標志：3004, 3605

完整備份

先清空錯誤日志

1 EXEC [sys].[sp_cycle_errorlog]

執行數據庫完整備份

1  DBCC traceon(3004, 3605, -1)
2  --完整備份
3  BACKUP DATABASE [pratice]
4  TO DISK='D:\pratice_fullbackup_201207160152.bak'

讀取錯誤日志

1 EXEC [sys].[xp_readerrorlog]

備份時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 2 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 2 上)處理了 2 頁。
3 BACKUP DATABASE 成功處理了 28154 頁，花費 1.639 秒(134.195 MB/秒)。

錯誤日志輸出

 1 1 2013-12-01 19:17:50.300 spid56       DBCC TRACEON 3004, server process ID (SPID) 56. This is an informational message only; no user action is required.
 2  2 2013-12-01 19:17:50.300 spid56       DBCC TRACEON 3605, server process ID (SPID) 56. This is an informational message only; no user action is required.
 3  3 2013-12-01 19:17:50.300 spid56       BackupDatabase: Database pratice
 4  4 2013-12-01 19:17:50.320 spid56       Backup: Media open
 5  5 2013-12-01 19:17:50.320 spid56       Backup: Media ready to backup
 6  6 2013-12-01 19:17:50.320 spid56       Backup: Clearing differential bitmaps   ---正在清除DCM頁面的位信息，因為是完整備份，差異變更(Differential Changed Map，DCM)頁面  http://tech.it168.com/a2010/0921/1106/000001106857.shtml
 7  7 2013-12-01 19:17:50.330 spid56       Backup: Bitmaps cleared
 8  8 2013-12-01 19:17:50.330 spid56       BackupDatabase: Checkpoint done   --做checkpoint，將所有修改的數據都被寫入磁盤
 9  9 2013-12-01 19:17:50.330 spid56       Backup: Scanning allocation bitmaps  --掃描數據庫第3頁：GAM頁 和數據庫第4頁：SGAM頁
10 10 2013-12-01 19:17:50.340 spid56       Backup: Done with allocation bitmaps
11 11 2013-12-01 19:17:50.340 spid56       BackupDatabase: Work estimates done  --估計備份操作所需要的時間
12 12 2013-12-01 19:17:50.340 spid56       Backup: Leading metadata section done
13 13 2013-12-01 19:17:50.340 spid56       Backup:Copying data   --正在拷貝數據
14 14 2013-12-01 19:17:50.340 spid56       Backup: DBReaderCount = 1   --備份數據庫讀線程只有一個
15 15 2013-12-01 19:17:50.340 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
16 16 2013-12-01 19:17:51.970 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
17 17 2013-12-01 19:17:51.970 spid56       Padding MSDA with 65536 bytes
18 18 2013-12-01 19:17:51.970 spid56       BackupDatabase: Database files done
19 19 2013-12-01 19:17:51.980 spid56       慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
20 20  : Log files done
21 21 2013-12-01 19:17:51.980 spid56       Backup: Trailing config done  --后續配置完成
22 22 2013-12-01 19:17:51.980 spid56       Backup: MBC done
23 23 2013-12-01 19:17:51.990 spid56       BackupDatabase: Writing history records  --寫入備份歷史記錄到錯誤日志
24 24 2013-12-01 19:17:51.990 備份           Database backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), pages dumped: 28171, first LSN: 3087:10132:37, last LSN: 3087:10149:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {D:\pratice_fullbackup_201207160152   --pages dumped: 28171  BACKUP DATABASE 成功處理了 28154 頁，花費 1.639 秒(134.195 MB/秒)
25 25 2013-12-01 19:17:51.990 spid56       Writing backup history records  --寫入備份歷史記錄到msdb的相關備份表
26 26 2013-12-01 19:17:52.010 spid56       BackupDatabase: Finished

關鍵的語句我都做了一些解釋

2013-12-01 19:17:50.320 spid56 Backup: Clearing differential bitmaps ---正在清除DCM頁面的位信息，

因為是完整備份，差異變更(Differential Changed Map，DCM)頁面 http://tech.it168.com/a2010/0921/1106/000001106857.shtml

差異備份

1 --差異備份
2 Backup Database [pratice]
3 To disk='d:\pratice_Diff_20070908.bak'
4 With Differential

備份時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 40 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 1 頁。
3 BACKUP DATABASE WITH DIFFERENTIAL 成功處理了 41 頁，花費 0.016 秒(20.019 MB/秒)。

錯誤日志輸出

 1 2013-12-01 19:42:29.790 spid56       BackupDatabase: Database pratice
 2 2013-12-01 19:42:29.820 spid56       Backup: Media open
 3 2013-12-01 19:42:29.820 spid56       Backup: Media ready to backup
 4 2013-12-01 19:42:29.820 spid56       BackupDatabase: Checkpoint done
 5 2013-12-01 19:42:29.820 spid56       Backup: Scanning allocation bitmaps
 6 2013-12-01 19:42:29.830 spid56       Backup: Done with allocation bitmaps
 7 2013-12-01 19:42:29.830 spid56       BackupDatabase: Work estimates done
 8 2013-12-01 19:42:29.830 spid56       Backup: Leading metadata section done
 9 2013-12-01 19:42:29.830 spid56       Backup:Copying data
10 2013-12-01 19:42:29.830 spid56       Backup: DBReaderCount = 1
11 2013-12-01 19:42:29.830 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
12 2013-12-01 19:42:29.830 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
13 2013-12-01 19:42:29.830 spid56       Padding MSDA with 720896 bytes
14 2013-12-01 19:42:29.840 spid56       BackupDatabase: Database files done
15 2013-12-01 19:42:29.840 spid56       慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
16  : Log files done
17 2013-12-01 19:42:29.840 spid56       Backup: Trailing config done
18 2013-12-01 19:42:29.840 spid56       Backup: MBC done
19 2013-12-01 19:42:29.840 spid56       BackupDatabase: Writing history records
20 2013-12-01 19:42:29.840 備份           Database differential changes were backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), pages dumped: 139, first LSN: 3087:10184:34, last LSN: 3087:10200:1, full backup LSN: 3087:10159:37, number of dump devices: 1, device information: 
21 2013-12-01 19:42:29.850 spid56       Writing backup history records
22 2013-12-01 19:42:29.860 spid56       BackupDatabase: Finished

跟完整備份的錯誤日志差不多

日志備份

1 --日志備份，默認截斷日志
2 Backup Log [pratice]
3 To disk='d:\pratice_Log_20070908.bak'

備份時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 29 頁。
2 BACKUP LOG 成功處理了 29 頁，花費 0.008 秒(27.954 MB/秒)。

錯誤日志輸出

 1 2013-12-01 19:48:02.080 spid56       BackupLog: database pratice
 2 2013-12-01 19:48:02.100 spid56       BackupLog: SafeCheckpoint done
 3 2013-12-01 19:48:02.110 spid56       Backup: Media open
 4 2013-12-01 19:48:02.110 spid56       Backup: Media ready to backup
 5 2013-12-01 19:48:02.110 spid56       BackupLog: Work estimate done
 6 2013-12-01 19:48:02.110 spid56       Backup: Leading metadata section done
 7 2013-12-01 19:48:02.110 spid56       慂正靈潌g邐%s : Log files done
 8  : Log files done
 9 2013-12-01 19:48:02.110 spid56       Backup: Trailing config done
10 2013-12-01 19:48:02.110 spid56       Backup: MBC done
11 2013-12-01 19:48:02.120 spid56       BackupLog: Writing history records
12 2013-12-01 19:48:02.120 備份           Log was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:9742:1, last LSN: 3087:10200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'d:\pratice_Log_20070908.bak'}). This is an informational m
13 2013-12-01 19:48:02.120 spid56       Writing backup history records
14 2013-12-01 19:48:02.140 spid56       BackupLog: Finished

日志備份里只有這句話跟差異備份和完整備份不一樣

1 2013-12-01 19:48:02.100 spid56       BackupLog: SafeCheckpoint done

文件備份

1 --文件備份
2 Exec Sp_Helpdb pratice --查看數據文件
3 Backup Database pratice
4 File='pratice'   --數據文件的邏輯名
5 To disk='d:\pratice_File_20070908.bak'

備份時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 BACKUP DATABASE...FILE=<name> 成功處理了 28154 頁，花費 1.663 秒(132.258 MB/秒)。

錯誤日志輸出

 1 2013-12-01 20:53:13.480 spid56       BackupDatabase: Database pratice
 2 2013-12-01 20:53:13.500 spid56       Backup: Media open
 3 2013-12-01 20:53:13.500 spid56       Backup: Media ready to backup
 4 2013-12-01 20:53:13.500 spid56       Backup: Clearing differential bitmaps
 5 2013-12-01 20:53:13.500 spid56       Backup: Bitmaps cleared
 6 2013-12-01 20:53:13.510 spid56       BackupDatabase: Checkpoint done
 7 2013-12-01 20:53:13.510 spid56       Backup: Scanning allocation bitmaps
 8 2013-12-01 20:53:13.510 spid56       Backup: Done with allocation bitmaps
 9 2013-12-01 20:53:13.510 spid56       BackupDatabase: Work estimates done
10 2013-12-01 20:53:13.510 spid56       Backup: Leading metadata section done
11 2013-12-01 20:53:13.510 spid56       Backup:Copying data
12 2013-12-01 20:53:13.520 spid56       Backup: DBReaderCount = 1
13 2013-12-01 20:53:13.520 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
14 2013-12-01 20:53:15.200 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
15 2013-12-01 20:53:15.200 spid56       Padding MSDA with 65536 bytes
16 2013-12-01 20:53:15.200 spid56       BackupDatabase: Database files done
17 2013-12-01 20:53:15.210 spid56       慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
18  : Log files done
19 2013-12-01 20:53:15.210 spid56       Backup: Trailing config done
20 2013-12-01 20:53:15.210 spid56       Backup: MBC done
21 2013-12-01 20:53:15.220 spid56       BackupDatabase: Writing history records
22 2013-12-01 20:53:15.220 備份           Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio
23 2013-12-01 20:53:15.230 spid56       Writing backup history records
24 2013-12-01 20:53:15.240 spid56       BackupDatabase: Finished

文件組備份

1 --文件組備份
2 Exec Sp_Helpdb pratice --查看數據文件
3 Backup Database pratice
4 FileGroup='Primary'   --數據文件的邏輯名
5 To disk='d:\pratice_FileGroup_20070908.bak'
6 With init

備份時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 BACKUP DATABASE...FILE=<name> 成功處理了 28154 頁，花費 1.679 秒(130.998 MB/秒)。

錯誤日志輸出

 1 2013-12-01 20:58:49.790 spid56       BackupDatabase: Database pratice
 2 2013-12-01 20:58:49.800 spid56       Backup: Media open
 3 2013-12-01 20:58:49.800 spid56       Backup: Media ready to backup
 4 2013-12-01 20:58:49.800 spid56       Backup: Clearing differential bitmaps
 5 2013-12-01 20:58:49.810 spid56       Backup: Bitmaps cleared
 6 2013-12-01 20:58:49.810 spid56       BackupDatabase: Checkpoint done
 7 2013-12-01 20:58:49.810 spid56       Backup: Scanning allocation bitmaps
 8 2013-12-01 20:58:49.820 spid56       Backup: Done with allocation bitmaps
 9 2013-12-01 20:58:49.820 spid56       BackupDatabase: Work estimates done
10 2013-12-01 20:58:49.820 spid56       Backup: Leading metadata section done
11 2013-12-01 20:58:49.820 spid56       Backup:Copying data
12 2013-12-01 20:58:49.820 spid56       Backup: DBReaderCount = 1
13 2013-12-01 20:58:49.820 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
14 2013-12-01 20:58:51.480 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
15 2013-12-01 20:58:51.480 spid56       Padding MSDA with 65536 bytes
16 2013-12-01 20:58:51.480 spid56       BackupDatabase: Database files done
17 2013-12-01 20:58:51.480 spid56       慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
18  : Log files done
19 2013-12-01 20:58:51.480 spid56       Backup: Trailing config done
20 2013-12-01 20:58:51.480 spid56       Backup: MBC done
21 2013-12-01 20:58:51.500 spid56       BackupDatabase: Writing history records
22 2013-12-01 20:58:51.500 備份           Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio
23 2013-12-01 20:58:51.500 spid56       Writing backup history records
24 2013-12-01 20:58:51.520 spid56       BackupDatabase: Finished

在完整備份和差異備份里面為什麼，查詢窗口輸出的“成功處理頁面”比錯誤日志里pages dumped的頁面還要少？？

就算加上數據庫頭部的一些關鍵頁面PFS 、DCM、 BCM、 IAM頁面加起來也沒有那麼多

數據庫頭部的一些關鍵頁面PFS 、DCM、 BCM、 IAM參考文章：查看SQLSERVER內部數據頁面的小插件Internals Viewer

下面是完整備份時候的輸出，多了17頁

BACKUP DATABASE 成功處理了 28154 頁
pages dumped: 28171

其實文章里也有提示：還原/備份時做了些什么

比如，你有個1TB的數據庫，但是只儲存了100GB的數據，那么備份的時候，只需要備份這100GB的數據。

然而，在還原數據庫的時候，你必須重構1TB的數據庫，那將意味著大量的時間將被消耗

這篇文章也有一些提示：誰用掉了我的數據庫空間？

SQL Server在使用數據頁的時候，為了提高速度，會先把一些頁面一次預留”reserve”給表格，然后真正有數據插入的時候，再使用

查詢窗口里輸出的是實際就是數據庫真正存儲數據的頁面的數量

BACKUP DATABASE 成功處理了 28154 頁

而錯誤日志就是所有頁面，不論是存儲數據還是PFS頁面還是預留頁面

相關實驗：下面的實驗創建了一個3GB大小的數據庫，然后插入了一些數據到數據庫中

（1）不對數據庫進行收縮，直接備份數據庫，然后還原

（2）先對數據庫進行收縮，備份數據庫，然后還原

USE master
GO
--創建數據庫 初始大小為3G
CREATE DATABASE [testbackupdb] ON PRIMARY 
( NAME = N'testbackupdb', 
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb.mdf' , SIZE = 3072000KB , FILEGROWTH = 1024KB )
    LOG ON 
( NAME = N'testbackupdb_log', 
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

--創建表
USE [testbackupdb]
GO

CREATE TABLE aa (id INT,NAME NVARCHAR(MAX))
GO

--插入數據
INSERT INTO aa
SELECT 1,REPLICATE('aa',500)
GO 10000

--查看表使用的空間 大概26MB
sys.sp_spaceused aa

sys.sp_helpdb [testbackupdb]


--備份數據庫
BACKUP DATABASE [testbackupdb] TO DISK ='d:\testbackupdb_fullbackup.bak' 


--刪除數據庫
DROP DATABASE [testbackupdb]



--1、還原數據庫   沒有收縮數據庫的情況下
SET STATISTICS TIME ON
RESTORE DATABASE [testbackupdb] FROM  DISK = N'D:\testbackupdb_fullbackup.bak' WITH  FILE = 1, 
MOVE N'testbackupdb' TO N'D:\testbackupdb.mdf',  
MOVE N'testbackupdb_log' TO N'D:\testbackupdb_1.ldf', 
NOUNLOAD,  REPLACE,  STATS = 10
GO
SET STATISTICS TIME OFF

 SQL Server 執行時間:
   CPU 時間 = 140 毫秒，占用時間 = 3024 毫秒。
SQL Server 分析和編譯時間: 
   CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
-------------------------------------------------------------------------------------------
--收縮數據庫為100MB
USE [testbackupdb]
GO
DBCC SHRINKFILE (N'testbackupdb' , 100)
GO


--備份數據庫
BACKUP DATABASE [testbackupdb] TO DISK ='d:\testbackupdb_fullbackup.bak' 


--刪除數據庫
DROP DATABASE [testbackupdb]

--2、還原數據庫   沒有收縮數據庫的情況下
SET STATISTICS TIME ON
RESTORE DATABASE [testbackupdb] FROM  DISK = N'D:\testbackupdb_fullbackup.bak' WITH  FILE = 1, 
MOVE N'testbackupdb' TO N'D:\testbackupdb.mdf',  
MOVE N'testbackupdb_log' TO N'D:\testbackupdb_1.ldf', 
NOUNLOAD,  REPLACE,  STATS = 10
GO
SET STATISTICS TIME OFF

 SQL Server 執行時間:
   CPU 時間 = 140 毫秒，占用時間 = 1549 毫秒。
SQL Server 分析和編譯時間: 
   CPU 時間 = 0 毫秒，占用時間 = 10 毫秒。



--查看數據庫大小
sys.sp_spaceused aa

sys.sp_helpdb [testbackupdb]

--結論：備份數據庫前先收縮，還原數據庫的時候會比較快
--因為收縮了數據庫之后，數據庫的size變成了收縮之后的大小，其實還原數據庫的時候
--SQLSERVER根據根據這個初始大小先初始化出這個數據庫初始大小的磁盤空間，然后再復制數據

View Code

結論：備份數據庫前先收縮，還原數據庫的時候會比較快
因為收縮了數據庫之后，數據庫的size變成了收縮之后的大小，其實還原數據庫的時候
SQLSERVER根據根據這個初始大小先初始化出這個數據庫初始大小的磁盤空間，然后再復制數據

數據庫還原時候的信息

執行下面腳本先kill掉所有連接到pratice數據庫的連接

 1 --執行下面腳本先kill掉所有連接到pratice數據庫的連接
 2 USE master
 3 GO
 4 DECLARE @spid INT
 5 DECLARE @count INT
 6 WHILE @count > 0
 7     BEGIN
 8         SELECT  @count = COUNT(*)
 9         FROM    sys.[sysprocesses]
10         WHERE   [dbid] = DB_ID('pratice')
11         SELECT  @spid = [spid]
12         FROM    sys.[sysprocesses]
13         WHERE   [dbid] = DB_ID('pratice')
14         EXEC('kill '+@spid)
15     END

1 --完整備份還原
2 RESTORE DATABASE [pratice]
3 FROM DISK ='D:\pratice_backup_201207160152.bak' WITH REPLACE

還原的時候，查詢窗口的輸出

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 RESTORE DATABASE 成功處理了 28154 頁，花費 2.267 秒(97.020 MB/秒)。

錯誤日志輸出

 1 2013-12-01 21:20:47.490 spid56       RestoreDatabase: Database pratice
 2 2013-12-01 21:20:47.490 spid56       Opening backup set  --打開備份集
 3 2013-12-01 21:20:47.500 spid56       SetTargetRestoreAge: 0
 4 2013-12-01 21:20:47.500 spid56       Restore: Configuration section loaded  --讀取數據庫配置，例如數據庫的初始大小
 5 2013-12-01 21:20:47.500 spid56       Restore: Backup set is open
 6 2013-12-01 21:20:47.500 spid56       Restore: Planning begins
 7 2013-12-01 21:20:47.520 spid56       Halting FullText crawls on database pratice  --因為pratice數據庫使用了全文搜索，這里會Halting停止全文搜索抓取
 8 2013-12-01 21:20:47.520 spid56       Dismounting FullText catalogs  --卸載FullText目錄
 9 2013-12-01 21:20:47.520 spid56       X-locking database: pratice    --用排他鎖鎖住pratice數據庫
10 2013-12-01 21:20:47.520 spid56       Resuming any halted fulltext crawls  恢復全文搜索抓取
11 2013-12-01 21:21:28.310 spid56       RestoreDatabase: Database pratice
12 2013-12-01 21:21:28.310 spid56       Opening backup set
13 2013-12-01 21:21:28.320 spid56       SetTargetRestoreAge: 0
14 2013-12-01 21:21:28.320 spid56       Restore: Configuration section loaded
15 2013-12-01 21:21:28.320 spid56       Restore: Backup set is open
16 2013-12-01 21:21:28.320 spid56       Restore: Planning begins
17 2013-12-01 21:21:28.340 spid56       Halting FullText crawls on database pratice
18 2013-12-01 21:21:28.340 spid56       Dismounting FullText catalogs
19 2013-12-01 21:21:28.340 spid56       X-locking database: pratice
20 2013-12-01 21:21:28.340 spid56       Restore: Planning complete
21 2013-12-01 21:21:28.340 spid56       Restore: BeginRestore (offline) on pratice
22 2013-12-01 21:21:28.340 spid56       AppDomain 2 (pratice.dbo[runtime].1) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.                    --卸載CLR
23 2013-12-01 21:21:28.350 spid20s      AppDomain 2 (pratice.dbo[runtime].1) unloaded.
24 2013-12-01 21:21:28.360 spid56       Restore: PreparingContainers  --準備容器
25 2013-12-01 21:21:28.390 spid56       Restore: Containers are ready --容器準備完成
26 2013-12-01 21:21:28.390 spid56       Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 1 to 6528 (0x2000 to 0x3300000) --日志填零初始化
27 2013-12-01 21:21:28.430 spid56       Restore: Restoring backup set
28 2013-12-01 21:21:28.430 spid56       Restore: Transferring data to pratice   --傳輸數據到pratice數據庫
29 2013-12-01 21:21:28.570 spid56       Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf 
30 http://blogs.msdn.com/b/apgcdsd/archive/2013/06/17/sql-server-log-write.aspx
31 2013-12-01 21:21:30.670 spid56       Restore: Waiting for log zero on pratice
32 2013-12-01 21:21:30.680 spid56       Restore: LogZero complete  --日志填零完畢
33 2013-12-01 21:21:30.680 spid56       SetTargetRestoreAge: 0
34 2013-12-01 21:21:30.700 spid56       FileHandleCache: 0 files opened. CacheSize: 12
35 2013-12-01 21:21:30.700 spid56       Restore: Data transfer complete on pratice  --數據傳輸完畢
36 2013-12-01 21:21:30.710 spid56       Restore: Backup set restored  --備份集已經還原
37 2013-12-01 21:21:30.720 spid56       Starting up database 'pratice'.  --啟動pratice數據庫
38 2013-12-01 21:21:30.780 spid56       The database 'pratice' is marked RESTORING and is in a state that does not allow recovery to be run.  --數據庫處于正在還原狀態，還未能完全恢復進行運行
39 --SQLSERVER數據庫主要狀態和切換路徑 http://www.cnblogs.com/lyhabc/archive/2012/09/15/2687076.html
40 2013-12-01 21:21:30.780 spid56       Restore-Redo begins on database pratice  --重做日志!!
41 2013-12-01 21:21:30.790 spid56       RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0xc0f:0x27dd:0x25
42 2013-12-01 21:21:30.790 spid56       RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0xc0f:0x27ee:0x1
43 2013-12-01 21:21:30.790 spid56       OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0xc0f:0x27ee:0x1
44 2013-12-01 21:21:30.790 spid56       Rollforward complete on database pratice --為什麼會有Rollforward http://www.cnblogs.com/lyhabc/p/3448449.html
45 2013-12-01 21:21:30.810 spid56       Restore: Done with fixups  --checkdb檢查完畢 fixup：修正
46 2013-12-01 21:21:30.810 spid56       Restore: Transitioning database to ONLINE
47 2013-12-01 21:21:30.810 spid56       Restore: Restarting database for ONLINE
48 2013-12-01 21:21:30.820 spid56       Starting up database 'pratice'.
49 2013-12-01 21:21:30.860 spid56       FixupLogTail(progress) zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from 0x1efdc00 to 0x1efe000.                           --修正日志尾部
50 2013-12-01 21:21:30.860 spid56       Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 3967 to 4447 (0x1efe000 to 0x22be000)
51 2013-12-01 21:21:30.870 spid56       Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
52 2013-12-01 21:21:30.870 spid56       PostRestoreContainerFixups: fixups complete
53 2013-12-01 21:21:30.870 spid56       CHECKDB for database 'pratice' finished without errors on 2013-11-23 23:35:18.670 (local time). This is an informational message only; no user action is required.
54 2013-12-01 21:21:30.880 spid56       PostRestoreReplicationFixup for pratice starts  --復制相關
55 2013-12-01 21:21:30.970 spid56       PostRestoreReplicationFixup for pratice complete
56 2013-12-01 21:21:30.970 spid56       Restore: Database is restarted   --數據庫已經重啟
57 2013-12-01 21:21:30.970 備份           Restore is complete on database 'pratice'.  The database is now available.  --數據庫還原完成，數據庫現在是可用狀態
58 2013-12-01 21:21:30.980 spid56       Resuming any halted fulltext crawls   --恢復全文搜索抓取
59 2013-12-01 21:21:30.990 spid56       Restore: Writing history records    --還原數據庫記錄寫入msdb里相關的表
60 2013-12-01 21:21:30.990 備份           Database was restored: Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:10205:37, last LSN: 3087:10222:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\pratice_backup_201207160152.bak'}). Informational  
61 2013-12-01 21:21:30.990 spid56       Writing backup history records    --備份歷史記錄寫入到錯誤日志
62 2013-12-01 21:21:31.010 spid56       Restore: Done with MSDB maintenance   
63 2013-12-01 21:21:31.010 spid56       RestoreDatabase: Finished

還有兩個跟蹤標志

跟蹤標志：3014、3604

為了節省篇幅我就補貼出剛才兩個跟蹤標志的信息了，只貼出3014、3604這兩個跟蹤標志輸出的信息

1 dbcc traceon(3014, 3604, -1)

完整備份

 1 2013-12-01 22:22:03.300 spid56       Calculating expected total data to write  --計算需要寫入備份設備的數據量
 2 2013-12-01 22:22:03.300 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)  --文件1 要備份的區 精確的diff位
 3 2013-12-01 22:22:03.300 spid56       Total (230621184)
 4 2013-12-01 22:22:03.300 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
 5 2013-12-01 22:22:03.300 spid56       BackupStream(0): Starting MSDA of size 3520 extents  --備份流 要備份的區大小
 6 2013-12-01 22:22:05.010 spid56       BackupStream(0): Total MSDA: 3520 extents
 7 2013-12-01 22:22:05.010 spid56       Backup data streams: Initial=230621184 Final=230621184 ExcessMode=0
 8 2013-12-01 22:22:05.010 spid56       Calculating expected total data to write
 9 2013-12-01 22:22:05.010 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
10 2013-12-01 22:22:05.010 spid56       Total (230621184)
11 2013-12-01 22:22:05.010 spid56       BackupDatabase: Database files done
12 2013-12-01 22:22:05.010 spid56       BackupLog: Family(0) StreamSize=0x10000
13 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
14 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
15 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
16 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
17 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
18 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
19 2013-12-01 22:22:05.020 spid56       BackupLog: Family(0) StreamSize=0x0
20 
21 2013-12-01 22:22:05.040 spid56       BackupDatabase: Writing history records
22 2013-12-01 22:22:05.040 備份           Database backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), pages dumped: 28171, first LSN: 3087:10237:43, last LSN: 3087:10256:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\pratice_fullbackup_201207160152
23 2013-12-01 22:22:05.040 spid56       Writing backup history records
24 2013-12-01 22:22:05.070 spid56       BackupDatabase: Finished

差異備份

備份時候，查詢窗口的輸出

 1 BackupDatabase: Database pratice
 2 
 3 Backup: Media open
 4 
 5 Backup: Media ready to backup
 6 
 7 BackupDatabase: Checkpoint done
 8 
 9 Backup: Scanning allocation bitmaps
10 
11 Backup: Done with allocation bitmaps
12 
13 Calculating expected total data to write
14 
15 Fid(1) ExpectedExt(5) DiffMapAccurate(1)
16 
17 Total (327680)
18 
19 BackupDatabase: Work estimates done
20 
21 Backup: Leading metadata section done
22 
23 Calculating expected total data to write
24 
25 Fid(1) ExpectedExt(5) DiffMapAccurate(1)
26 
27 Total (327680)
28 
29 Backup:Copying data
30 
31 Backup: DBReaderCount = 1
32 
33 Backup data streams: Initial=327680 Final=327680 ExcessMode=0
34 
35 Calculating expected total data to write
36 
37 Fid(1) ExpectedExt(5) DiffMapAccurate(1)
38 
39 Total (327680)
40 
41 BackupDatabase: Database files done
42 
43 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 40 頁。
44 BackupLog: Family(0) StreamSize=0x10000
45 
46 BackupLog: Family(0) StreamSize=0x0
47 
48 BackupLog: Family(0) StreamSize=0x0
49 
50 BackupLog: Family(0) StreamSize=0x0
51 
52 BackupLog: Family(0) StreamSize=0x0
53 
54 BackupLog: Family(0) StreamSize=0x0
55 
56 BackupLog: Family(0) StreamSize=0x0
57 
58 BackupLog: Family(0) StreamSize=0x0
59 
60 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 1 頁。
61 慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
62  : Log files done
63 
64 Backup: Trailing config done
65 
66 Backup: MBC done
67 
68 BackupDatabase: Writing history records
69 
70 Writing backup history records
71 
72 BACKUP DATABASE WITH DIFFERENTIAL 成功處理了 41 頁，花費 0.023 秒(13.926 MB/秒)。
73 BackupDatabase: Finished

錯誤日志輸出

 1 
 2 2013-12-01 22:33:04.500 spid56       Calculating expected total data to write
 3 2013-12-01 22:33:04.500 spid56       Fid(1) ExpectedExt(5) DiffMapAccurate(1)
 4 2013-12-01 22:33:04.500 spid56       Total (327680)
 5 2013-12-01 22:33:04.510 spid56       BackupStream(0): Starting MSDA of size 16 extents
 6 2013-12-01 22:33:04.510 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
 7 2013-12-01 22:33:04.510 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
 8 2013-12-01 22:33:04.510 spid56       Padding MSDA with 720896 bytes
 9 2013-12-01 22:33:04.510 spid56       BackupStream(0): Total MSDA: 16 extents
10 2013-12-01 22:33:04.510 spid56       Backup data streams: Initial=327680 Final=327680 ExcessMode=0
11 2013-12-01 22:33:04.510 spid56       Calculating expected total data to write
12 2013-12-01 22:33:04.510 spid56       Fid(1) ExpectedExt(5) DiffMapAccurate(1)
13 2013-12-01 22:33:04.510 spid56       Total (327680)
14 2013-12-01 22:33:04.510 spid56       BackupDatabase: Database files done
15 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x10000
16 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
17 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
18 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
19 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
20 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
21 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
22 2013-12-01 22:33:04.510 spid56       BackupLog: Family(0) StreamSize=0x0
23 
24 2013-12-01 22:33:04.510 spid56       Backup: Trailing config done
25 2013-12-01 22:33:04.510 spid56       Backup: MBC done
26 2013-12-01 22:33:04.530 spid56       BackupDatabase: Writing history records
27 2013-12-01 22:33:04.530 備份           Database differential changes were backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), pages dumped: 139, first LSN: 3087:10264:34, last LSN: 3087:10280:1, full backup LSN: 3087:10237:43, number of dump devices: 1, device information: 
28 2013-12-01 22:33:04.530 spid56       Writing backup history records
29 2013-12-01 22:33:04.540 spid56       BackupDatabase: Finished

日志備份

備份時候，查詢窗口的輸出

 1 BackupLog: database pratice
 2 
 3 BackupLog: SafeCheckpoint done
 4 
 5 Backup: Media open
 6 
 7 Backup: Media ready to backup
 8 
 9 BackupLog: Work estimate done
10 
11 Backup: Leading metadata section done
12 
13 BackupLog: Family(0) StreamSize=0x10000
14 
15 BackupLog: Family(0) StreamSize=0x0
16 
17 BackupLog: Family(0) StreamSize=0x0
18 
19 BackupLog: Family(0) StreamSize=0x0
20 
21 BackupLog: Family(0) StreamSize=0x0
22 
23 BackupLog: Family(0) StreamSize=0x0
24 
25 BackupLog: Family(0) StreamSize=0x0
26 
27 BackupLog: Family(0) StreamSize=0x0
28 
29 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 4 頁。
30 慂正靈潌g邐%s : Log files done
31  : Log files done
32 
33 Backup: Trailing config done
34 
35 Backup: MBC done
36 
37 BackupLog: Writing history records
38 
39 Writing backup history records
40 
41 BACKUP LOG 成功處理了 4 頁，花費 0.011 秒(2.574 MB/秒)。
42 BackupLog: Finished

錯誤日志輸出

 1 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x10000
 2 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 3 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 4 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 5 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 6 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 7 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 8 2013-12-01 22:35:59.010 spid56       BackupLog: Family(0) StreamSize=0x0
 9 2013-12-01 22:35:59.020 spid56       BackupLog: Writing history records
10 2013-12-01 22:35:59.020 備份           Log was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:10222:1, last LSN: 3087:10280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'d:\pratice_Log_20070908.bak'}). This is an informational 
11 2013-12-01 22:35:59.020 spid56       Writing backup history records
12 2013-12-01 22:35:59.040 spid56       BackupLog: Finished

文件備份

備份時候，查詢窗口的輸出

 1 BackupDatabase: Database pratice
 2 
 3 Backup: Media open
 4 
 5 Backup: Media ready to backup
 6 
 7 Backup: Clearing differential bitmaps
 8 
 9 Backup: Bitmaps cleared
10 
11 BackupDatabase: Checkpoint done
12 
13 Backup: Scanning allocation bitmaps
14 
15 Backup: Done with allocation bitmaps
16 
17 Calculating expected total data to write
18 
19 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
20 
21 Total (230621184)
22 
23 BackupDatabase: Work estimates done
24 
25 Backup: Leading metadata section done
26 
27 Calculating expected total data to write
28 
29 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
30 
31 Total (230621184)
32 
33 Backup:Copying data
34 
35 Backup: DBReaderCount = 1
36 
37 Backup data streams: Initial=230621184 Final=230621184 ExcessMode=0
38 
39 Calculating expected total data to write
40 
41 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
42 
43 Total (230621184)
44 
45 BackupDatabase: Database files done
46 
47 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
48 BackupLog: Family(0) StreamSize=0x10000
49 
50 BackupLog: Family(0) StreamSize=0x0
51 
52 BackupLog: Family(0) StreamSize=0x0
53 
54 BackupLog: Family(0) StreamSize=0x0
55 
56 BackupLog: Family(0) StreamSize=0x0
57 
58 BackupLog: Family(0) StreamSize=0x0
59 
60 BackupLog: Family(0) StreamSize=0x0
61 
62 BackupLog: Family(0) StreamSize=0x0
63 
64 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
65 慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
66  : Log files done
67 
68 Backup: Trailing config done
69 
70 Backup: MBC done
71 
72 BackupDatabase: Writing history records
73 
74 Writing backup history records
75 
76 BACKUP DATABASE...FILE=<name> 成功處理了 28154 頁，花費 1.601 秒(137.380 MB/秒)。
77 BackupDatabase: Finished

錯誤日志輸出

 1 2013-12-01 22:37:54.430 spid56       Calculating expected total data to write
 2 2013-12-01 22:37:54.430 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
 3 2013-12-01 22:37:54.430 spid56       Total (230621184)
 4 2013-12-01 22:37:54.430 spid56       Calculating expected total data to write
 5 2013-12-01 22:37:54.430 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
 6 2013-12-01 22:37:54.430 spid56       Total (230621184)
 7 2013-12-01 22:37:54.430 spid56       Backup:Copying data
 8 2013-12-01 22:37:54.440 spid56       Backup: DBReaderCount = 1
 9 2013-12-01 22:37:54.440 spid56       BackupStream(0): Starting MSDA of size 3520 extents
10 2013-12-01 22:37:56.020 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
11 2013-12-01 22:37:56.030 spid56       Padding MSDA with 65536 bytes
12 2013-12-01 22:37:56.030 spid56       BackupStream(0): Total MSDA: 3520 extents
13 2013-12-01 22:37:56.030 spid56       Backup data streams: Initial=230621184 Final=230621184 ExcessMode=0
14 2013-12-01 22:37:56.030 spid56       Calculating expected total data to write
15 2013-12-01 22:37:56.030 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
16 2013-12-01 22:37:56.030 spid56       Total (230621184)
17 2013-12-01 22:37:56.030 spid56       BackupDatabase: Database files done
18 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x10000
19 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
20 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
21 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
22 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
23 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
24 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
25 2013-12-01 22:37:56.030 spid56       BackupLog: Family(0) StreamSize=0x0
26 2013-12-01 22:37:56.030 spid56       BackupDatabase: Writing history records
27 2013-12-01 22:37:56.030 備份           Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio
28 2013-12-01 22:37:56.050 spid56       Writing backup history records
29 2013-12-01 22:37:56.080 spid56       BackupDatabase: Finished

文件組備份

備份時候，查詢窗口的輸出

 1 BackupDatabase: Database pratice
 2 
 3 Backup: Media open
 4 
 5 Backup: Media ready to backup
 6 
 7 Backup: Clearing differential bitmaps
 8 
 9 Backup: Bitmaps cleared
10 
11 BackupDatabase: Checkpoint done
12 
13 Backup: Scanning allocation bitmaps
14 
15 Backup: Done with allocation bitmaps
16 
17 Calculating expected total data to write
18 
19 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
20 
21 Total (230621184)
22 
23 BackupDatabase: Work estimates done
24 
25 Backup: Leading metadata section done
26 
27 Calculating expected total data to write
28 
29 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
30 
31 Total (230621184)
32 
33 Backup:Copying data
34 
35 Backup: DBReaderCount = 1
36 
37 Backup data streams: Initial=230621184 Final=230621184 ExcessMode=0
38 
39 Calculating expected total data to write
40 
41 Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
42 
43 Total (230621184)
44 
45 BackupDatabase: Database files done
46 
47 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
48 BackupLog: Family(0) StreamSize=0x10000
49 
50 BackupLog: Family(0) StreamSize=0x0
51 
52 BackupLog: Family(0) StreamSize=0x0
53 
54 BackupLog: Family(0) StreamSize=0x0
55 
56 BackupLog: Family(0) StreamSize=0x0
57 
58 BackupLog: Family(0) StreamSize=0x0
59 
60 BackupLog: Family(0) StreamSize=0x0
61 
62 BackupLog: Family(0) StreamSize=0x0
63 
64 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
65 慂正靈慄慴慢敳退慂正靈潌g邐%s : Log files done
66  : Log files done
67 
68 Backup: Trailing config done
69 
70 Backup: MBC done
71 
72 BackupDatabase: Writing history records
73 
74 Writing backup history records
75 
76 BACKUP DATABASE...FILE=<name> 成功處理了 28154 頁，花費 1.676 秒(131.232 MB/秒)。
77 BackupDatabase: Finished

錯誤日志輸出

 1 2013-12-01 22:40:04.260 spid56       Calculating expected total data to write
 2 2013-12-01 22:40:04.260 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
 3 2013-12-01 22:40:04.260 spid56       Total (230621184)
 4 2013-12-01 22:40:04.260 spid56       Calculating expected total data to write
 5 2013-12-01 22:40:04.260 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
 6 2013-12-01 22:40:04.260 spid56       Total (230621184)
 7 2013-12-01 22:40:04.260 spid56       Backup:Copying data
 8 2013-12-01 22:40:04.270 spid56       Backup: DBReaderCount = 1
 9 2013-12-01 22:40:04.270 spid56       Started file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
10 2013-12-01 22:40:04.270 spid56       BackupStream(0): Starting MSDA of size 3520 extents
11 2013-12-01 22:40:05.920 spid56       Completed file D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
12 2013-12-01 22:40:05.930 spid56       Padding MSDA with 65536 bytes
13 2013-12-01 22:40:05.930 spid56       BackupStream(0): Total MSDA: 3520 extents
14 2013-12-01 22:40:05.930 spid56       Backup data streams: Initial=230621184 Final=230621184 ExcessMode=0
15 2013-12-01 22:40:05.930 spid56       Calculating expected total data to write
16 2013-12-01 22:40:05.930 spid56       Fid(1) ExpectedExt(3519) DiffMapAccurate(0)
17 2013-12-01 22:40:05.930 spid56       Total (230621184)
18 2013-12-01 22:40:05.930 spid56       BackupDatabase: Database files done
19 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x10000
20 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
21 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
22 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
23 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
24 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
25 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
26 2013-12-01 22:40:05.930 spid56       BackupLog: Family(0) StreamSize=0x0
27 2013-12-01 22:40:05.930 spid56       Backup: Trailing config done
28 2013-12-01 22:40:05.930 spid56       Backup: MBC done
29 2013-12-01 22:40:05.940 spid56       BackupDatabase: Writing history records
30 2013-12-01 22:40:05.940 備份           Database file was backed up. Database: pratice, creation date(time): 2013/11/19(19:35:23), file list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: 28171, number of dump devices: 1, device informatio
31 2013-12-01 22:40:05.940 spid56       Writing backup history records
32 2013-12-01 22:40:05.960 spid56       BackupDatabase: Finished

數據庫還原

還原的時候，查詢窗口的輸出

 1 Restore: Planning complete
 2 
 3 Restore: BeginRestore (offline) on pratice
 4 
 5 Restore: PreparingContainers
 6 
 7 Restore: Containers are ready
 8 
 9 Restore: Restoring backup set
10 
11 Restore: Transferring data to pratice
12 
13 FileHandleCache: 0 files opened. CacheSize: 12
14 
15 Restore: Data transfer complete on pratice
16 
17 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
18 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
19 Restore: Backup set restored
20 
21 Restore-Redo begins on database pratice
22 
23 RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0xc0f:0x27fd:0x2b
24 
25 RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0xc0f:0x2810:0x1
26 
27 OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0xc0f:0x2810:0x1
28 
29 Rollforward complete on database pratice
30 
31 Restore: Done with fixups
32 
33 Restore: Transitioning database to ONLINE
34 
35 Restore: Restarting database for ONLINE
36 
37 Restore: Database is restarted
38 
39 Resuming any halted fulltext crawls
40 
41 Restore: Writing history records
42 
43 Writing backup history records
44 
45 Restore: Done with MSDB maintenance
46 
47 RESTORE DATABASE 成功處理了 28154 頁，花費 2.317 秒(94.927 MB/秒)。
48 RestoreDatabase: Finished

錯誤日志輸出

 1 2013-12-01 22:42:32.430 spid56       Restore: PreparingContainers
 2 2013-12-01 22:42:32.460 spid56       Restore: Containers are ready
 3 2013-12-01 22:42:32.460 spid56       Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 1 to 6528 (0x2000 to 0x3300000)
 4 2013-12-01 22:42:32.500 spid56       Restore: Restoring backup set
 5 2013-12-01 22:42:32.500 spid56       Restore: Transferring data to pratice
 6 2013-12-01 22:42:32.530 spid56       Starting MSDA in stream 0, thread 5164, length 0xdc00000
 7 2013-12-01 22:42:32.620 spid56       Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
 8 2013-12-01 22:42:34.790 spid56       Completed MSDA in stream 0, thread 5164
 9 2013-12-01 22:42:34.790 spid56       Restore: Waiting for log zero on pratice
10 2013-12-01 22:42:34.800 spid56       Restore: LogZero complete
11 2013-12-01 22:42:34.800 spid56       SetTargetRestoreAge: 0
12 2013-12-01 22:42:34.820 spid56       FileHandleCache: 0 files opened. CacheSize: 12
13 2013-12-01 22:42:34.820 spid56       Restore: Data transfer complete on pratice
14 2013-12-01 22:42:34.830 spid56       Restore: Backup set restored
15 2013-12-01 22:42:34.840 spid56       Starting up database 'pratice'.
16 2013-12-01 22:42:34.890 spid56       The database 'pratice' is marked RESTORING and is in a state that does not allow recovery to be run.
17 2013-12-01 22:42:34.900 spid56       Restore-Redo begins on database pratice
18 2013-12-01 22:42:34.900 spid56       RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0xc0f:0x27fd:0x2b
19 2013-12-01 22:42:34.900 spid56       RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0xc0f:0x2810:0x1
20 2013-12-01 22:42:34.900 spid56       OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0xc0f:0x2810:0x1
21 2013-12-01 22:42:34.900 spid56       Rollforward complete on database pratice
22 2013-12-01 22:42:34.920 spid56       Restore: Done with fixups
23 2013-12-01 22:42:34.920 spid56       Restore: Transitioning database to ONLINE
24 2013-12-01 22:42:34.920 spid56       Restore: Restarting database for ONLINE
25 2013-12-01 22:42:34.940 spid56       Starting up database 'pratice'.
26 2013-12-01 22:42:34.980 spid56       Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf from page 3969 to 4449 (0x1f02000 to 0x22c2000)
27 2013-12-01 22:42:34.980 spid56       Zeroing completed on D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
28 2013-12-01 22:42:35.000 spid56       PostRestoreContainerFixups: fixups complete
29 2013-12-01 22:42:35.000 spid56       CHECKDB for database 'pratice' finished without errors on 2013-11-23 23:35:18.670 (local time). This is an informational message only; no user action is required.
30 2013-12-01 22:42:35.000 spid56       PostRestoreReplicationFixup for pratice starts
31 2013-12-01 22:42:35.120 spid56       PostRestoreReplicationFixup for pratice complete
32 2013-12-01 22:42:35.120 spid56       Restore: Database is restarted
33 2013-12-01 22:42:35.120 備份           Restore is complete on database 'pratice'.  The database is now available.
34 2013-12-01 22:42:35.130 spid56       Resuming any halted fulltext crawls
35 2013-12-01 22:42:35.130 spid56       Restore: Writing history records
36 2013-12-01 22:42:35.130 備份           Database was restored: Database: pratice, creation date(time): 2013/11/19(19:35:23), first LSN: 3087:10237:43, last LSN: 3087:10256:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\pratice_fullbackup_201207160152.bak'}). Informati
37 2013-12-01 22:42:35.130 spid56       Writing backup history records
38 2013-12-01 22:42:35.160 spid56       Restore: Done with MSDB maintenance
39 2013-12-01 22:42:35.160 spid56       RestoreDatabase: Finished

還原時候輸出的日志好像跟剛才兩個跟蹤標志輸出的日志沒有什么差別

跟蹤標志3213查看Transfer Size

Transfer Size即是數據傳輸大小，備份的時候從mdf文件的數據傳輸到bak備份文件的傳輸單位大小

1 DBCC traceon(3605, 3004, 3014, 3213, -1)

這幾個跟蹤標志要一起打開，不能只打開3213，不然顯示不出信息的

備份

1 --完整備份
2 BACKUP DATABASE [pratice]
3 TO DISK='D:\pratice_fullbackup_201207160152.bak'

錯誤日志輸出

 1 2013-12-01 22:57:32.810 spid56       Backup/Restore buffer configuration parameters
 2 2013-12-01 22:57:32.810 spid56       Memory limit: 188MB
 3 2013-12-01 22:57:32.810 spid56       BufferCount:                7
 4 2013-12-01 22:57:32.810 spid56       Sets Of Buffers:            1
 5 2013-12-01 22:57:32.810 spid56       MaxTransferSize:            1024 KB
 6 2013-12-01 22:57:32.810 spid56       Min MaxTransferSize:        64 KB
 7 2013-12-01 22:57:32.810 spid56       Total buffer space:         7 MB
 8 2013-12-01 22:57:32.810 spid56       Tabular data device count:  1
 9 2013-12-01 22:57:32.810 spid56       Fulltext data device count: 0
10 2013-12-01 22:57:32.810 spid56       Filestream device count:    0
11 2013-12-01 22:57:32.810 spid56       TXF device count:           0
12 2013-12-01 22:57:32.810 spid56       Filesystem i/o alignment:   512
13 2013-12-01 22:57:32.810 spid56       Media Buffer count:            7
14 2013-12-01 22:57:32.810 spid56       Media Buffer size:          1024KB

還原

 1 --執行下面腳本先kill掉所有連接到pratice數據庫的連接
 2 USE master
 3 GO
 4 DECLARE @spid INT
 5 DECLARE @count INT
 6 WHILE @count > 0
 7     BEGIN
 8         SELECT  @count = COUNT(*)
 9         FROM    sys.[sysprocesses]
10         WHERE   [dbid] = DB_ID('pratice')
11         SELECT  @spid = [spid]
12         FROM    sys.[sysprocesses]
13         WHERE   [dbid] = DB_ID('pratice')
14         EXEC('kill '+@spid)
15     END
16 
17 --完整備份還原
18 RESTORE DATABASE [pratice]
19 FROM DISK ='D:\pratice_fullbackup_201207160152.bak' WITH REPLACE

錯誤日志輸出

 1 2013-12-01 23:07:29.890 spid57       Backup/Restore buffer configuration parameters
 2 2013-12-01 23:07:29.890 spid57       Memory limit: 188MB
 3 2013-12-01 23:07:29.890 spid57       BufferCount:                6
 4 2013-12-01 23:07:29.890 spid57       Sets Of Buffers:            1
 5 2013-12-01 23:07:29.890 spid57       MaxTransferSize:            1024 KB
 6 2013-12-01 23:07:29.890 spid57       Min MaxTransferSize:        64 KB
 7 2013-12-01 23:07:29.890 spid57       Total buffer space:         6 MB
 8 2013-12-01 23:07:29.890 spid57       Tabular data device count:  1
 9 2013-12-01 23:07:29.890 spid57       Fulltext data device count: 0
10 2013-12-01 23:07:29.890 spid57       Filestream device count:    0
11 2013-12-01 23:07:29.890 spid57       TXF device count:           0
12 2013-12-01 23:07:29.890 spid57       Filesystem i/o alignment:   512
13 2013-12-01 23:07:29.890 spid57       Media Buffer count:            6
14 2013-12-01 23:07:29.890 spid57       Media Buffer size:          1024KB

參考文章：SQL Server Backup/Restore具體做什么

如有不對的地方，歡迎大家拍磚o(∩_∩)o

----------------------------------------------------------------------------------------

2013-12-2晚更正和補充：

這里謝謝QQ群里的高大俠

在完整備份的錯誤日志解釋里面有一句話解釋錯了

2013-12-01 19:17:50.330 spid56 BackupDatabase: Checkpoint done --做checkpoint，將已提交事務的數據寫入磁盤，未提交事務的繼續留在內存

應該改為

2013-12-01 19:17:50.330 spid56 BackupDatabase: Checkpoint done --做checkpoint，將所有修改的數據都被寫入磁盤

為什麼這樣改？？

大家可以參考一下宋大師寫的文章：

SQL Server CheckPoint的幾個誤區

再談SQL Server中日志的的作用

----------------------------------------------------------------------------------------------------

在文章開頭的那篇文章：還原/備份時做了些什么 說到了

SQL Server的備份和還原是如何選擇Transfer Size的

一個客戶問道：為什么Transfer Size在備份時設置為1MB，還原時為64KB，除非我修改BUFFERCOUNT 和MAXTRANSFERSIZE?

我們指定MAXTRANSFERSIZE為最大的數值和不指定的情況下看一下備份和還原所需要的時間和輸出的日志

備份和還原時候所消耗內存我就不測試了，我這里僅僅測試time

至于MAXTRANSFERSIZE的最大值是多少可以看一下下面的《SQL SERVER BACKUP DATABASE 加快備份速度方法》

備份比較

指定的情況下：

1 --完整備份
2 SET STATISTICS TIME ON
3 BACKUP DATABASE [pratice]
4 TO DISK='D:\pratice_fullbackup_201207160152.bak'  
5 WITH BUFFERCOUNT = 50, MAXTRANSFERSIZE = 4194304
6 SET STATISTICS TIME OFF

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 BACKUP DATABASE 成功處理了 28154 頁，花費 1.377 秒(159.728 MB/秒)。
4 
5  SQL Server 執行時間:
6    CPU 時間 = 63 毫秒，占用時間 = 1437 毫秒。
7 
8  SQL Server 執行時間:
9    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

 1 2013-12-02 20:16:57.700 spid58       Memory limit: 188MB
 2 2013-12-02 20:16:57.700 spid58       BufferCount:                50
 3 2013-12-02 20:16:57.700 spid58       Sets Of Buffers:            1
 4 2013-12-02 20:16:57.700 spid58       MaxTransferSize:            4096 KB
 5 2013-12-02 20:16:57.700 spid58       Min MaxTransferSize:        64 KB
 6 2013-12-02 20:16:57.700 spid58       Total buffer space:         200 MB
 7 2013-12-02 20:16:57.700 spid58       Tabular data device count:  1
 8 2013-12-02 20:16:57.700 spid58       Fulltext data device count: 0
 9 2013-12-02 20:16:57.700 spid58       Filestream device count:    0
10 2013-12-02 20:16:57.700 spid58       TXF device count:           0
11 2013-12-02 20:16:57.700 spid58       Filesystem i/o alignment:   512
12 2013-12-02 20:16:57.700 spid58       Media Buffer count:           50
13 2013-12-02 20:16:57.700 spid58       Media Buffer size:          4096KB

MaxTransferSize: 4096 KB=4MB

BufferCount: 50

不指定的情況下：

1 --完整備份
2 SET STATISTICS TIME ON
3 BACKUP DATABASE [pratice]
4 TO DISK='D:\pratice_fullbackup_201207160152.bak'  
5 SET STATISTICS TIME OFF

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 BACKUP DATABASE 成功處理了 28154 頁，花費 1.685 秒(130.531 MB/秒)。
4 
5  SQL Server 執行時間:
6    CPU 時間 = 77 毫秒，占用時間 = 1742 毫秒。
7 
8  SQL Server 執行時間:
9    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

 1 2013-12-02 20:21:22.090 spid58       Memory limit: 188MB
 2 2013-12-02 20:21:22.090 spid58       BufferCount:                7
 3 2013-12-02 20:21:22.090 spid58       Sets Of Buffers:            1
 4 2013-12-02 20:21:22.090 spid58       MaxTransferSize:            1024 KB
 5 2013-12-02 20:21:22.090 spid58       Min MaxTransferSize:        64 KB
 6 2013-12-02 20:21:22.090 spid58       Total buffer space:         7 MB
 7 2013-12-02 20:21:22.090 spid58       Tabular data device count:  1
 8 2013-12-02 20:21:22.090 spid58       Fulltext data device count: 0
 9 2013-12-02 20:21:22.090 spid58       Filestream device count:    0
10 2013-12-02 20:21:22.090 spid58       TXF device count:           0
11 2013-12-02 20:21:22.090 spid58       Filesystem i/o alignment:   512
12 2013-12-02 20:21:22.090 spid58       Media Buffer count:            7
13 2013-12-02 20:21:22.090 spid58       Media Buffer size:          1024KB

MaxTransferSize: 1024 KB=1MB

BufferCount: 7

默認的時候MaxTransferSize確實是1MB大小：為什么Transfer Size在備份時設置為1MB，還原時為64KB，除非我修改BUFFERCOUNT 和MAXTRANSFERSIZE?

所用時間比較：

還原比較

指定的情況下：

1 --完整備份還原
2 SET STATISTICS TIME ON
3 RESTORE DATABASE [pratice]
4 FROM DISK ='D:\pratice_fullbackup_201207160152.bak' 
5 WITH REPLACE,BUFFERCOUNT = 50, MAXTRANSFERSIZE = 4194304
6 SET STATISTICS TIME OFF

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 RESTORE DATABASE 成功處理了 28154 頁，花費 1.691 秒(130.068 MB/秒)。
4 
5  SQL Server 執行時間:
6    CPU 時間 = 140 毫秒，占用時間 = 2118 毫秒。

 1 2013-12-02 20:39:52.740 spid58       Memory limit: 188MB
 2 2013-12-02 20:39:52.740 spid58       BufferCount:                50
 3 2013-12-02 20:39:52.740 spid58       Sets Of Buffers:            1
 4 2013-12-02 20:39:52.740 spid58       MaxTransferSize:            4096 KB
 5 2013-12-02 20:39:52.740 spid58       Min MaxTransferSize:        64 KB
 6 2013-12-02 20:39:52.740 spid58       Total buffer space:         200 MB
 7 2013-12-02 20:39:52.740 spid58       Tabular data device count:  1
 8 2013-12-02 20:39:52.740 spid58       Fulltext data device count: 0
 9 2013-12-02 20:39:52.740 spid58       Filestream device count:    0
10 2013-12-02 20:39:52.740 spid58       TXF device count:           0
11 2013-12-02 20:39:52.740 spid58       Filesystem i/o alignment:   512
12 2013-12-02 20:39:52.740 spid58       Media Buffer count:           50
13 2013-12-02 20:39:52.740 spid58       Media Buffer size:          4096KB

不指定的情況下：

1 --完整備份還原
2 SET STATISTICS TIME ON
3 RESTORE DATABASE [pratice]
4 FROM DISK ='D:\pratice_fullbackup_201207160152.bak' 
5 WITH REPLACE
6 SET STATISTICS TIME OFF

1 已為數據庫 'pratice'，文件 'pratice' (位于文件 1 上)處理了 28152 頁。
2 已為數據庫 'pratice'，文件 'pratice_log' (位于文件 1 上)處理了 2 頁。
3 RESTORE DATABASE 成功處理了 28154 頁，花費 2.240 秒(98.190 MB/秒)。
4 
5  SQL Server 執行時間:
6    CPU 時間 = 94 毫秒，占用時間 = 2636 毫秒。

 1 2013-12-02 20:41:51.890 spid58       Memory limit: 188MB
 2 2013-12-02 20:41:51.890 spid58       BufferCount:                6
 3 2013-12-02 20:41:51.890 spid58       Sets Of Buffers:            1
 4 2013-12-02 20:41:51.890 spid58       MaxTransferSize:            1024 KB
 5 2013-12-02 20:41:51.890 spid58       Min MaxTransferSize:        64 KB
 6 2013-12-02 20:41:51.890 spid58       Total buffer space:         6 MB
 7 2013-12-02 20:41:51.890 spid58       Tabular data device count:  1
 8 2013-12-02 20:41:51.890 spid58       Fulltext data device count: 0
 9 2013-12-02 20:41:51.890 spid58       Filestream device count:    0
10 2013-12-02 20:41:51.890 spid58       TXF device count:           0
11 2013-12-02 20:41:51.890 spid58       Filesystem i/o alignment:   512
12 2013-12-02 20:41:51.890 spid58       Media Buffer count:            6
13 2013-12-02 20:41:51.890 spid58       Media Buffer size:          1024KB

MaxTransferSize: 1024 KB=1MB

BufferCount: 6

還原時為64KB，但是從錯誤日志里只看出了Min TransferSize為64KB

不過從下圖可以看到還原時候buffer count比備份時候少了1

時間比較

可以看出無論是備份還是還原，指定MaxTransferSize比不指定MaxTransferSize都要快

是不是指定MAXTRANSFERSIZE = 4194304就是最好的？

還原/備份時做了些什么 文章中給了一些建議

不同的設備類型有其自己的GetSuggestedIoSize實現方式.

硬盤

讀/寫操作有不同的默認值

如果設備處于讀狀態（還原），或者這是個Desktop或者Express版本，使用64K

如果設備處于寫狀態（備份），而且既不是Desktop也不是Express版本，使用1MB。（更大的寫操作容量將降低文件的拓展性，而且不利于NTFS的元數據的維護性）

磁帶

備份或者還原都是用64K除非磁帶介質上的Block Size設置為其他的默認值。

VDI

備份或者還原都是用64K。

當BUFFERCOUNT沒有被設置時，每種設備類型有自己的實現方式。.

每個設備都暴露一個GetSuggestedIoDepth成員來在BUFFERCOUNT未被設置時來決定默認值。

硬盤

深度默認為3

磁帶

深度默認為1

VDI

默認為1，最大為4，基于設備的跨度需求。

推薦值會做相應修改。

備份數據庫

bufferCount += backupDeviceCount

bufferCount += 2 * databaseDeviceCount

還原數據庫或者還原文件

bufferCount += 2 * databaseDeviceCount

備份日志或者還原日志

bufferCount += 2 * backupDeviceCount

當目標確立后，他們會調整Transfer Size和Block Counts來確定每個Steam都會在適當的內存分配下有緩存來進行工作。

最大可使用容量（緩存池的物理內存 / 16）。參見GlobalMemoryStatusEx (ullTotalPhys )。這是這臺機器上所有的物理內存。所以如果你為一個64GM的機器開啟/PAE，而SQL Server的實例沒有使用AWE，有可能更多的物理內存將會試圖允許更大的備份只是因為/PAE被開啟。這個問題你依然在緩存池之外的內存中，它將會限制或者使備份的嘗試在大的MAXTRANSFERSIXE和大BUFFERCOUNT下失敗。

如果計算超出了內存的1/16，容量將會被調整到一個合理的緩存和Transfer Size。

我做了一些測試，默認值在不同的配置文件下被合理的設置以滿足備份和還原在性能上的需要。你可以調整這些參數來符合你對特殊系統的性能需要。

-------------------------------------------------------------------------------------------------------

SQL SERVER BACKUP DATABASE 加快備份速度方法

最近在研究了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），保證數據安全。

1 --完整備份
2 BACKUP DATABASE [pratice]
3 TO DISK='D:\pratice_fullbackup_201207160152.bak'
4 WITH CHECKSUM ,COMPRESSION

1 --還原的時候要指定CHECKSUM選項才可以檢查校驗和
2 RESTORE DATABASE pratice FROM DISK='C:\pratice_fullbackup_201207160152.bak' 
3 WITH REPLACE ,CHECKSUM, RECOVERY
4 GO

在備份和還原期間可能的介質錯誤 (SQL Server)

備份校驗和

SQL Server 支持三種校驗和：頁校驗和、日志塊校驗和以及備份校驗和。

日志塊

生成備份校驗和時，BACKUP 將驗證從數據庫讀取的數據是否與數據庫中存在的任意校驗和或頁殘缺指示一致。

向備份介質寫入頁之前，備份操作將驗證頁級信息（頁校驗和或頁殘缺檢測）是否存在。

如果兩者都不存在，則備份無法驗證頁。將按原樣包含未經驗證的頁，并且其內容將添加到總備份校驗和中。

如果備份操作在驗證過程中遇到頁錯誤，備份將失敗

備份集標記為包含備份校驗和（在 msdb..backupset 的 has_backup_checksums 列中）

將備份介質中的備份集標記為包含錯誤，并跟蹤 msdb 數據庫的 suspect_pages 表中的頁

默認情況下，在遇到頁校驗和錯誤后，BACKUP 或 RESTORE 操作將失敗，而 RESTORE VERIFYONLY 操作將繼續。

但是，您可以控制某一給定操作在遇到錯誤時是失敗還是盡可能繼續。

---------------------------------------------------------------------------------------------------

SQLSERVER備份語句的詳細語法以及各個with選項的詳細解釋

http://msdn.microsoft.com/zh-cn/library/ms186865.aspx

 1 BACKUP LOG { database_name | @database_name_var } 
 2   TO <backup_device> [ ,...n ] 
 3   [ <MIRROR TO clause> ] [ next-mirror-to ]
 4   [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
 5 [;]
 6 
 7 <backup_device>::= 
 8  {
 9    { logical_device_name | @logical_device_name_var } 
10  | { DISK | TAPE | URL} = 
11      { 'physical_device_name' | @physical_device_name_var }
12  } 
13 Note: URL is the format used to specify the location and the file name for the Windows Azure Blob. Although Windows Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seemless backup experince for all the three devices. This option requires WITH CREDENTIAL argument. 
14 
15 <MIRROR TO clause>::=
16  MIRROR TO <backup_device> [ ,...n ]
17 
18 <file_or_filegroup>::=
19  {
20    FILE = { logical_file_name | @logical_file_name_var } 
21  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
22  } 
23 
24 <read_only_filegroup>::=
25 FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
26 
27 <general_WITH_options> [ ,...n ]::=  
28 --Backup Set Options
29    COPY_ONLY 
30  | { COMPRESSION | NO_COMPRESSION } 
31  | DESCRIPTION = { 'text' | @text_variable } 
32  | NAME = { backup_set_name | @backup_set_name_var } 
33  | { EXPIREDATE = { 'date' | @date_var } 
34         | RETAINDAYS = { days | @days_var } } 
35 
36 --Media Set Options
37    { NOINIT | INIT } 
38  | { NOSKIP | SKIP } 
39  | { NOFORMAT | FORMAT } 
40  | MEDIADESCRIPTION = { 'text' | @text_variable } 
41  | MEDIANAME = { media_name | @media_name_variable } 
42  | BLOCKSIZE = { blocksize | @blocksize_variable } 
43 
44 --Data Transfer Options
45    BUFFERCOUNT = { buffercount | @buffercount_variable } 
46  | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
47 
48 --Error Management Options
49    { NO_CHECKSUM | CHECKSUM }
50  | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
51 
52 --Compatibility Options
53    RESTART 
54 
55 --Monitoring Options
56    STATS [ = percentage ] 
57 
58 --Tape Options
59    { REWIND | NOREWIND } 
60  | { UNLOAD | NOUNLOAD } 
61 
62 --Log-specific Options
63    { NORECOVERY | STANDBY = undo_file_name }
64  | NO_TRUNCATE
65 
66 --Encryption Options
67  ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options ) 
68 <encryptor_options> ::= 
69    SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

View Code

--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }

-------------------------------------------------------------------------------------------------------

SQLSERVER還原語句的詳細語法以及各個with選項的詳細解釋

http://msdn.microsoft.com/zh-cn/library/ms186858(v=sql.120).aspx

  1 --To Restore an Entire Database from a Full database backup (a Complete Restore):
  2 RESTORE DATABASE { database_name | @database_name_var } 
  3  [ FROM <backup_device> [ ,...n ] ]
  4  [ WITH 
  5    {
  6     [ RECOVERY | NORECOVERY | STANDBY = 
  7         {standby_file_name | @standby_file_name_var } 
  8        ]
  9    | ,  <general_WITH_options> [ ,...n ]
 10    | , <replication_WITH_option>
 11    | , <change_data_capture_WITH_option>
 12    | , <FILESTREAM_WITH_option>
 13    | , <service_broker_WITH options> 
 14    | , <point_in_time_WITH_options—RESTORE_DATABASE> 
 15    } [ ,...n ]
 16  ]
 17 [;]
 18 
 19 --To perform the first step of the initial restore sequence 
 20 -- of a piecemeal restore:
 21 RESTORE DATABASE { database_name | @database_name_var } 
 22    <files_or_filegroups> [ ,...n ]
 23  [ FROM <backup_device> [ ,...n ] ] 
 24    WITH 
 25       PARTIAL, NORECOVERY 
 26       [  , <general_WITH_options> [ ,...n ] 
 27        | , <point_in_time_WITH_options—RESTORE_DATABASE> 
 28       ] [ ,...n ] 
 29 [;]
 30 
 31 --To Restore Specific Files or Filegroups: 
 32 RESTORE DATABASE { database_name | @database_name_var } 
 33    <file_or_filegroup> [ ,...n ]
 34  [ FROM <backup_device> [ ,...n ] ] 
 35    WITH 
 36    {
 37       [ RECOVERY | NORECOVERY ]
 38       [ , <general_WITH_options> [ ,...n ] ]
 39    } [ ,...n ] 
 40 [;]
 41 
 42 --To Restore Specific Pages: 
 43 RESTORE DATABASE { database_name | @database_name_var } 
 44    PAGE = 'file:page [ ,...n ]' 
 45  [ , <file_or_filegroups> ] [ ,...n ]
 46  [ FROM <backup_device> [ ,...n ] ] 
 47    WITH 
 48        NORECOVERY   
 49       [ , <general_WITH_options> [ ,...n ] ]
 50 [;]
 51 
 52 --To Restore a Transaction Log:
 53 RESTORE LOG { database_name | @database_name_var } 
 54  [ <file_or_filegroup_or_pages> [ ,...n ] ]
 55  [ FROM <backup_device> [ ,...n ] ] 
 56  [ WITH 
 57    {
 58      [ RECOVERY | NORECOVERY | STANDBY = 
 59         {standby_file_name | @standby_file_name_var } 
 60        ]
 61     | ,  <general_WITH_options> [ ,...n ]
 62     | , <replication_WITH_option>
 63     | , <point_in_time_WITH_options—RESTORE_LOG> 
 64    } [ ,...n ]
 65  ] 
 66 [;]
 67 
 68 --To Revert a Database to a Database Snapshot:   
 69 RESTORE DATABASE { database_name | @database_name_var } 
 70 FROM DATABASE_SNAPSHOT = database_snapshot_name  
 71 
 72 <backup_device>::=
 73 { 
 74    { logical_backup_device_name |
 75       @logical_backup_device_name_var }
 76  | { DISK | TAPE | URL } = { 'physical_backup_device_name' |
 77       @physical_backup_device_name_var } 
 78 } 
 79 Note: URL is the format used to specify the location and the file name for the Windows Azure Blob. Although Windows Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seemless restore experince for all the three devices. 
 80 <files_or_filegroups>::= 
 81 { 
 82    FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var } 
 83  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } 
 84  | READ_WRITE_FILEGROUPS
 85 } 
 86 
 87 <general_WITH_options> [ ,...n ]::=  
 88 --Restore Operation Options
 89    MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
 90           [ ,...n ] 
 91  | REPLACE 
 92  | RESTART 
 93  | RESTRICTED_USER 
 94  | CREDENTIAL
 95 
 96 --Backup Set Options
 97  | FILE = { backup_set_file_number | @backup_set_file_number } 
 98  | PASSWORD = { password | @password_variable } 
 99 
100 --Media Set Options
101  | MEDIANAME = { media_name | @media_name_variable } 
102  | MEDIAPASSWORD = { mediapassword | @mediapassword_variable } 
103  | BLOCKSIZE = { blocksize | @blocksize_variable } 
104 
105 --Data Transfer Options
106  | BUFFERCOUNT = { buffercount | @buffercount_variable } 
107  | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
108 
109 --Error Management Options
110  | { CHECKSUM | NO_CHECKSUM } 
111  | { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } 
112 
113 --Monitoring Options
114  | STATS [ = percentage ] 
115 
116 --Tape Options
117  | { REWIND | NOREWIND } 
118  | { UNLOAD | NOUNLOAD } 
119   
120 <replication_WITH_option>::=
121  | KEEP_REPLICATION 
122 
123 <change_data_capture_WITH_option>::=
124  | KEEP_CDC
125 
126 <FILESTREAM_WITH_option>::=
127  | FILESTREAM ( DIRECTORY_NAME = directory_name )
128 
129 
130 <service_broker_WITH_options>::= 
131  | ENABLE_BROKER 
132  | ERROR_BROKER_CONVERSATIONS 
133  | NEW_BROKER 
134 
135 
136 <point_in_time_WITH_options—RESTORE_DATABASE>::= 
137  | {
138    STOPAT = { 'datetime'| @datetime_var } 
139  | STOPATMARK = 'lsn:lsn_number'
140                  [ AFTER 'datetime'] 
141  | STOPBEFOREMARK = 'lsn:lsn_number'
142                  [ AFTER 'datetime'] 
143    } 
144 
145 <point_in_time_WITH_options—RESTORE_LOG>::= 
146  | {
147    STOPAT = { 'datetime'| @datetime_var } 
148  | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
149                  [ AFTER 'datetime'] 
150  | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
151                  [ AFTER 'datetime'] 
152    }