About Automatic Recovery

Microsoft SQL Server provides two different types of recovery:

• Automatic recovery occurs every time SQL Server is restarted. This type of recovery protects your database in the event of system failures.
• User-initiated means you back up the database and its log at regular intervals and then restore the database and its log when necessary. (You can perform the backups, or scheduled them to occur on a one-time or recurring basis.)

  Regular backups provide the means of recovering data in case of media failure (as do the disk storage methods¾for example, hardware-based RAID or Windows NT-based mirroring¾discussed in Chapter 7, Managing Drives.)

Whenever SQL Server is restarted, recovery is performed automatically on each database. This does the following:

• Rolls back uncommitted transactions¾those that were ongoing at the time SQL Server was shut down or when the system failed.
• Checks the transactions that had been committed between the last checkpoint and the failure, and rolls them forward.

  (In each database, the automatic recovery mechanism looks at the transaction log. If the log has committed transactions not yet written out to the database, it performs those transactions again. This action is known as rolling forward.)

Automatic recovery begins with the master database, goes on to model, clears out the tempdb temporary database, recovers msdb, recovers pubs, recovers distribution (if the server is configured as a replication distributor), and, finally, recovers user databases. Users can log in to SQL Server as soon as the system databases have been recovered, but they cannot access a user database while recovery is in progress on it.

Two configuration options are relevant to automatic recovery:

• The recovery flags option, which determines what information SQL Server displays during recovery.
• The recovery interval option, which controls the maximum time required to recover a database by setting the interval at which SQL Server decides whether to run an automatic checkpoint.

These two options are set by using SQL Enterprise Manager or sp_configure. For more information, see Chapter 3, Configuring Servers.