SQL Server Backup/Restore具体做什么

zyh3033

微软提供了一些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 Process
http://www.remotedbaexperts.com/Blog/2011/03/undocumented-trace-flags-inside-the-restore-process/