Understanding the SQL Server Recover Models

There are three recovery models to choose from: Simple, Full, and Bulk-Logged. The choice of the model depends on various factors that need to be considered, such as the extent to which data loss is acceptable, performance of the SQL Server, and database recovery to the point of failure.

The simple recovery model recovers the database only to the point of the last successful full or differential backup. Content added to the database after the backup cannot be recovered with this model.

The full recovery model recovers the entire database to any point in time, because transaction logs are maintained. It must be noted that because all transactions to the database are logged, SQL Server database performance tends to degrade. It is recommended that transaction logs and the database files are stored on separate hard disks for performance enhancement and recovery.

The bulk logged recovery model is similar to the full recovery model because it maintains a transaction log; however, this model should be used only in situations where large amounts of data are written to the database. To improve performance of the database server in such situations of bulk insertion or indexing, the recovery model should be switched to the bulk logged model temporarily.

Business requirements define the disaster recovery strategies and drive the database administrator’s decision on choosing the appropriate recovery model for the database. By default, the SharePoint configuration, AdminContent, and site content databases’ recovery model is set to Full. As a result, these databases can be restored to the point of failure.

To set the recovery model on a SharePoint content database, perform the following steps:

1. Open the SQL Server Management Studio (Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio).
2. On the screen, select the database server to connect to the SQL database.
3. In the left pane of the Object Explorer, expand the server and then the database folder.
4. Select the desired SharePoint Database to back up. Right-click on the database, and select Properties.
5. In the Database properties dialog box, select the Options node.
6. In the Recovery Mode dialog box, select Full, Bulk-Logged, or Simple from the drop-down list. Full is typically selected in most cases. Click OK to save the changes.