User Tools

Site Tools


SQL Server Recovery Model

SQL Server supports different recovery models.

Recovery model Description Work loss exposure Recover to point in time?
Simple No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone. Can recover only to the end of a backup.
Full Requires log backups.

No work is lost due to a lost or damaged data file.
Can recover to an arbitrary point in time (for example, prior to application or user error).
Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.
Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
For more information, see Restoring a Database to a Point Within a Backup.
Bulk logged Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.
Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.
If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.
Can recover to the end of any backup.
Point-in-time recovery is not supported.

You find a lot of information about the topic on Microsoft TechNet. Here's an article about log file truncation.

/srv/ · Last modified: 2009/08/15 12:14 (external edit)