SQL Server Backup/Restore具體做什麼

微軟提供了一些Undocumented Trace Flags可以讓我們瞭解很多備份與還原的資訊，通過輸出的內容我們可以清楚的看到BACKUP/Restore都做了哪些事情。 因為這些是Undocumented ，所以微軟的建議是謹慎使用，如果要用的話需要有微軟工程師的指導，當然大家可以在自己的電腦上玩玩， 這樣可以看到備份與還原背後的故事。 這是我在自己電腦上做的測試：開啟TraceFlag

DBCC TRACEON(3004);

 

DBCC TRACEON(3014);

 

DBCC TRACEON(3604);

 

DBCC TRACEON(3605);

 

備份資料庫：

backup database AdventureWorks to disk ='d:\mssql\AdventureWorks.bak'

在Error log中可以看到下面的資訊：

BackupDatabase: Database AdventureWorks

Backup: Media open

Backup: Media ready to backup

Backup: Clearing differential bitmaps

Backup: Bitmaps cleared

BackupDatabase: Checkpoint done

Backup: Scanning allocation bitmaps

Backup: Done with allocation bitmaps

Calculating expected total data to write

Fid(1) ExpectedExt(2888) DiffMapAccurate(0)

Total (189267968)

BackupDatabase: Work estimates done

Backup: Leading metadata section done

Calculating expected total data to write

Fid(1) ExpectedExt(2888) DiffMapAccurate(0)

Total (189267968)

Backup:Copying data

Backup: DBReaderCount = 1

Backup data streams: Initial=189267968 Final=189267968 ExcessMode=0

Calculating expected total data to write

Fid(1) ExpectedExt(2888) DiffMapAccurate(0)

Total (189267968)

BackupDatabase: Database files done

Processed 23104 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 2.
BackupLog: Family(0) StreamSize=0x10000

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

BackupLog: Family(0) StreamSize=0x0

Processed 1 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 2.
BackupDatabase: Log files done

Backup: Trailing config done

Backup: MBC done

Writing backup history records

BACKUP DATABASE successfully processed 23105 pages in 2.169 seconds (83.221 MB/sec).
BackupDatabase: Finished

 然後做還原：

RestoreDatabase: Database AdventureWorks

Opening backup set

Restore: Configuration section loaded

Restore: Backup set is open

Restore: Planning begins

Halting FullText crawls on database AdventureWorks

Dismounting FullText catalogs

X-locking database: AdventureWorks

Restore: Planning complete

Restore: BeginRestore (offline) on AdventureWorks

Restore: PreparingContainers

Restore: Containers are ready

Restore: Restoring backup set

Restore: Transferring data to AdventureWorks

FileHandleCache: 0 files opened. CacheSize: 12

Restore: Data transfer complete on AdventureWorks

Processed 23104 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1.
Processed 6 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1.
Restore: Backup set restored

Restore-Redo begins on database AdventureWorks

Rollforward complete on database AdventureWorks

Restore: Done with fixups

Restore: Transitioning database to ONLINE

Restore: Restarting database for ONLINE

Restore: Database is restarted

Resuming any halted fulltext crawls

Restore: Writing history records

Writing backup history records

Restore: Done with MSDB maintenance

RESTORE DATABASE successfully processed 23110 pages in 2.531 seconds (71.331 MB/sec).
RestoreDatabase: Finished

 更多的Trace flag的資訊可以參考：Undocumented Trace Flags: Inside the Restore Processhttp://www.remotedbaexperts.com/Blog/2011/03/undocumented-trace-flags-inside-the-restore-process/