Does restoring a SQL database from backup rebuild its indexes?

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.

Solution 1:

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.

Solution 2:

A native SQL backup is just a page-by-page dump of the backup files, so the answer there is "no". A Quest lightspeed backup likely uses some sort of compression compression algorithm, but it still won't "rebuild" the data files or indexes, which would take a horrendously large amount of time on a big database.

Solution 3:

Backup is done regularly and very often (I hope). So designers made sure backup is as quick as possible. What is the quickest I/O? Sequential. You read blocks from disks in exact physical order, you have the best performance.

Why on Earth should database perform cumbersome random I/O operation every single night, trashing the disks' heads all over the place? The difference would be around two orders of magnitude. There is no possible gain in this.