Search This Blog

24 October 2010

SQL Server Database Recovery Models in Brief

SQL Server database recovery models gives backup and restore flexibility. The model used to determine how great your risk of data loss will be when a breakdown occurs. There are three types of database recovery models available
  1. Full Recovery
  2. Bulk_Logged Recovery
  3. Simple Recovery
Full Recovery Model :
In the Full recovery model, the database engine logs all operations onto the transaction log, and the database engine never truncates the log. The Full recovery model lets you restore a database to the point of failure
Simple Recovery Model :
In the Simple recovery model, the database engine minimally logs most operations and truncates the transaction log after each checkpoint. In the Simple recovery model, you cannot back up or restore the transaction log. Furthermore, you cannot restore individual data pages.
Bulk-Logged Recovery Model:
In the Bulk-Logged recovery model, the database engine minimally logs bulk operations such as SELECT INTO and BULK INSERT. In this recovery model, if a log backup contains any bulk operation, you can restore the database to the end of the log backup, not to a point in time. The Bulk-Logged recovery model is  intended to be used only during large bulk operations.

Important: The Simple recovery model is not appropriate for databases in which the loss of recent changes is unacceptable.

1 comment:

  1. sql repair can repair mdf files from any version of SQL Server. The program doesn't modify source data during recovering. It can show restored mdf and starts under all available MS Windows.