What does SQL Server backup/restore do?

Microsoft provides some uninitialized ented trace flags to let us know a lot about backup and Restoration Information. Through the output, we can clearly see what backup/restore has done. Because these are unreceivented, Microsoft's advice is to use them with caution. If you want to use them, you need guidance from Microsoft engineers. Of course, you can play on your own computer, in this way, you can see the story behind backup and restoration. This is my test on my computer: Enable traceflag

DBCC traceon (3004 );

 

DBCC traceon (3014 );

 

DBCC traceon (3604 );

 

DBCC traceon (3605 );

 

Back up the database:

Backup database adventureworks to disk = 'd: \ MSSQL \ adventureworks. Bak'

The following information is displayed in the 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

Then perform Restoration:

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 Crawler

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

For more information about trace flag, see unreceivented trace flags: Inside the restore processhttp: // www.remotedbaexperts.com/blog/2011/03/undeleented-trace-flags-inside-the-restore-process/