Tuesday, March 22, 2016

Recovery Models in SQL SERVER

We have three recovery models for database, Full, Bulk-logged and simple.

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, seeRestoring 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 supporte

No comments:

Post a Comment