Does restoring a SQL database from backup rebuild its tables and indexes from scratch? Or does it keep it in the same internal physical order it was in at the time of backup?
We're using SQL 2000 with Quest Lightspeed compressed backup, if that makes any difference.
Best Answer
The answer is no, for whatever backup software is being used.
A backup is a physical operation, not a logical operation. It reads all extents containing allocated pages (i.e. even though only a single page from an 8-page extent is allocated, it will backup the entire 64K extent), and it does it in physical order.
A restore is a physical operation, not a logical operation. It lays down the extents in their rightful places in the data files.
Rebuilding an index (or anything like it) is a logical operation, which must be logged. Backup and restore manipulate the data files directly, without going through the buffer pool, which is one reason why this cannot be done. Another reason this cannot be done is that backup and restore have no understanding of what is contained in the data being backed up.
The main reason this cannot be done, however, is that moving pages around during a restore operation would break the b-tree pointers. If page A points to page B, but page A is moved by the restore process, how is page B updated to point to page A? If it's updated right away, then it may be overwritten by the rest of the restore process. If it's deferred-updated, what if the restore process restored some transaction log that removed page A, or page B? It simply cannot be done.
Bottom line - backup and restore are physical operations that never change the data.
Hope this helps!
PS Although it doesn't directly address this question, check out the article I wrote for the July TechNet Magazine which explains how the various backups work internally: Understanding SQL Server Backups. The September magazine will have the next in the series on understanding restores.