Microsoft SQL Server 2012 Bible

Recovery Concepts

The concept of database recovery is based on the D in the transactional-integrity ACID properties — transactional durability. Durability means that a transaction, when committed, regardless of hardware failure, must be persistent.

SQL Server accomplishes transactional durability with a write-ahead transaction log. Every transaction is written to the transaction log prior to being written to the data file. This provides a few benefits to the recovery plan:

• The transaction log ensures that every transaction can be recovered up to the last moment before the server stopped.
• The transaction log permits backups while transactions are processed.
• The transaction log reduces the impact of a hardware failure because the transaction log and the data file may be placed on different disk subsystems.

The strategy of a recovery plan should be based on the organization's tolerance level, or pain level, for lost transactions. Recovery-plan tactics involve choosing among the various backup options, generating a backup schedule, and off-site storage.

SQL Server backup and recovery are flexible, offering three recovery models from which to choose. The transaction log can be configured, based on your recovery needs, according to one of the following recovery models:

• Simple: No transaction log backups.
• Bulk-logged: The bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions.
• Full: All transactions are logged.

In addition, SQL Server ...