Database recovery models are an important aspect of any SQL Server implementation. A recovery model is used to determine how you will back up the database as well as how you will recover data should an issue occur. This is all part of defining and measuring your database’s Recovery Point Objective, or RPO. An RPO allows you to gauge the amount of data that would be lost in the event of a system failure.
There are three types of Recovery Models available in SQL Server. The types, along with a brief description of each is shown below. If you are working in an older version of SQL Server you may not have all types available to you.
Database backup only. No transaction log backups are available. Can only restore full or differential backups. Simple is the easiest to manage but at the expense of higher data loss exposure.
Includes both transaction log backups and full backups. Full recovery allows for point-in-time recovery. With full recovery, SQL Server will preserve the transaction log until it is backed up. This is the most flexible option and it is best practice to use this model.
Recovery model that minimizes transaction log activity during bulk-operations. Similar to Full but generally used only temporarily while performing bulk-logged operations.
In order to configure the recovery model of a database you will first need to connect to the database in SQL Management Studio. Next, expand the Databases drop-down and locate the database that you would like to configure. Right-click it and select Properties.
Once the Database Properties window is displayed, click on the Options page from the list of available pages on the left. This screen will display several options that can be configured for the database including the collation (language), recovery model, compatibility level and containment type. For the purposes of this post I have highlighted the recovery model option.
Setting the desired recovery model is as simple as selecting it from the drop-down and clicking OK to save the changes. As a good rule of thumb you generally want to use either the Full or Simple recovery model. If point of failure recovery is not necessary and you do not want to backup and restore log transactions, the Simple model is the best choice. However, if you do want point of failure recovery as well as the ability to restore individual pages and filegroups, the Full recovery model is the way to go.
Bulk-logged is the less common recovery model. With this model bulk operations such as
SELECT INTO and
CREATE INDEX are not fully logged in the transaction log in order to minimize space. With the bulk-logged recovery model your transaction logs will not grow too much if you are doing bulk operations and it still allows for point in time recovery as long as the last transaction log doesn’t include any bulk logged operations. If no bulk operations are run then this recovery model is basically the same as the Full recovery model. Generally the bulk-logged recovery model is used temporarily while performing bulk-logged operations. The recovery model is then switched back to Full.
Another way you can identify the recovery model of a database is by running the following script. Here, I am checking the recovery model of a database named TSQL2012.
SELECT DATABASEPROPERTYEX('TSQL2012', 'Recovery') As [Recovery Model]
You can also change the recovery model of a database through scripting. The following script will set the TSQL2012 database to the Simple recovery model.
ALTER DATABASE TSQL2012
SET RECOVERY SIMPLE
Understanding the differences between the various recovery models is an important function of anyone administering a database. It can mean the difference between the loss of data and the ability to recover data to a particular point in time. If you are unsure as to which model to use, go with the Full recovery model.