SQL Server Full Recovery Model

By: Greg Robidoux

Overview

The "Full" recovery model tells SQL Server to keep all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated. The way this works is that all transactions that are issued against SQL Server first get entered into the transaction log and then the data is written to the appropriate data file.  This allows SQL Server to rollback each step of the process in case there was an error or the transaction was cancelled for some reason.  So, when the database is set to the "Full" recovery model, since all transactions have been saved you have the ability to do point in time recovery which means you can recover to a point right before a transaction occurred like an accidental deletion of all data from a table.

Explanation

The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable and readable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

Here are some reasons why you may choose this recovery model:

• Data is critical and you want to minimize data loss.
• You need the ability to do a point-in-time recovery.
• You are using Database Mirroring
• You are using Always On Availability Groups

Types of backups you can run when the data is in the "Full" recovery model:

• Complete backups
• Differential backups
• File and/or Filegroup backups
• Partial backups
• Copy-Only backups
• Transaction log backups

Set SQL Server Full Recovery Model using T-SQL

ALTER DATABASE dbName SET RECOVERY recoveryOption
GO

Example: change AdventureWorks database to "Full" recovery model

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Set SQL Server Full Recovery Model using Management Studio

• Right click on database name and select Properties
• Go to the Options page
• Under Recovery model select "Full"
• Click "OK" to save