Fuzzy Backup and Restore Operations

Microsoft® SQL Server™ version 7.0 uses industry-standard fuzzy backup algorithms. These new algorithms have several main benefits for users:

• The BACKUP statement runs faster and has less effect on users modifying data while the statement is processing.
• The RESTORE statement is faster.

A RESTORE operation restores the database to the state it was in at the time the BACKUP statement finished. In earlier versions of SQL Server, a LOAD statement restored a database to the state it was in at the time the DUMP statement started.

In a SQL Server fuzzy backup and restore operation:

• Extents containing data are written to the backup set without regard to synchronizing pages being modified by users during the backup. This significantly reduces the effect the backup has on current users. It also allows the backup to copy pages serially. The elimination of any random reads speeds the backup process in heavily used systems. It does mean, however, that the pages in the backup are stored in an inconsistent, unrecovered state.
• The transaction log is copied as part of the backup.

A RESTORE statement:

• Creates the database if it does not exist, and initializes the extents in the database. This step is bypassed if the database exists when the RESTORE statement is executed.
• Copies in the extents found in the backup set. The process is fast because all the extents are in a serial sequence. Extents not found in the backup set are ignored; they are not initialized as empty extents.
• Uses the transaction log to recover the database. The database modifications recorded in the log are rolled forward to the end of the end of the log, and then any incomplete transactions are rolled back. This returns the database to a consistent, recovered state that corresponds to the state the database was in at the time the BACKUP statement completed.