« Simple Recovery Model
SQL Server Full Backup »

SQL Server Recovery Models

A database can be set in one of three recovery modes: Full Recovery, Simple Recovery, and Bulk-Logged Recovery. Each mode determines how the transaction log should behave, both in active use and during a transaction log backup

  1. In Full Recovery Model, All transactions are logged and available for restore and truncated at transaction backup.
  2. In Bulk_Logged Recovery Model, All transactions except bulk-load events are logged and available for restore and truncated at backup.
  3. In Simple Recovery Model, All transactions are minimally logged for recovery operations and not available for restore and truncated when checkpoint occur.

Setting Recovery Model

  1. T-SQL
    2. Alter  Database dbName
  Set recovery  full; ( bulk_logged, or simple )
  ////View log  DBCC LOG(dbName)
  2. SSMS
    3.   Database  -->Properties --> Options Tab -->  Set  Recovery Model

Changing Recovery Model

Recommendation for changing recovery model:

  1. Switch from Full Recovery to Bulk-Logged Recovery.
  2. Perform your bulk-logged operation.
  3. After the bulk-logged operation completes, immediately move back to Full Recovery mode.
  4. Perform a full database backup.

Tags: , , , ,

This entry was posted on Saturday, January 5th, 2008 at 1:14 pm and is filed under Backup Database, SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply