What You Back Up Is What You Restore

I'm trying to come up with a database maintenance plan that runs as fast as possible. I've been told that restoring a full backup will reorganize the indexes so that I don't have to worry about index maintenance. Can you tell me what other operations are performed when a backup is restored?

There are quite a few misconceptions around backup and restore, and this is one of them. Simply put, you can think of backup and restore operations as "what you back up is what you get when you restore." Therefore, no optimizations are performed when the restore writes the data file pages into the database's data files.

The backup operation (whether full, differential, or a log backup after a minimally-logged operation) reads the data file pages from disk and doesn't even look at them, except to check any existing page checksums if you used the WITH CHECKSUM option in SQL Server 2005 and later. When the backup is restored, the data file pages are written back into the database's data file at exactly the same page offset and location as when they were read by the backup. The only changes that are potentially made to the data file pages are those required to perform recovery on the database to bring it to a transactionally consistent state when the restore sequence is complete.

It's possible that the restored copy of the database might have less NTFS-level file fragmentation if it's restored on a volume (or volumes, for multiple databases' data files) with enough contiguous free space to accommodate the new file(s). File fragmentation can have a small effect on scan performance but magnitudes less than index fragmentation within the database's data files. (You can learn more about how backup and restore operations work by viewing the Backup/Restore category of my blog at www.sqlskills.com/BLOGS/PAUL/category/BackupRestore.aspx.)

A related question I often get asked is whether it's possible to avoid performing consistency checks when using the WITH CHECKSUM option on backups of databases created on SQL Server 2005 or later where page checksums are enabled by default. Again, the answer is no. The reason is that it's possible that a page was corrupted in memory and then written out to disk with a valid checksum. Although the page appears to be valid, it is, in fact, corrupt, which only DBCC consistency checks can detect.

Discuss this Blog Entry 1

on Mar 25, 2010
It is always a pleasure to hear from this couple. Thanks a lot for all your posts!

Take Care!

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×