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.
