SQL Server Backup/Restore具体做什么

来源：互联网发布：数据分析师薪水编辑：程序博客网时间：2024/04/19 14:10

微软提供了一些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

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/