By: Greg Robidoux
Overview
One of the first things that needs to be set in order to create the correct backups is to set the proper recovery model for each database. The recovery model basically tells SQL Server what data to keep in the transaction log file and for how long. Based on the recovery model that is selected, this will also determine what types of backups you can perform and also what types of database restores can be performed.
Explanation
The three types of recovery models that you can choose from are:
Each database can have only one recovery model, but each of your databases can use a different recovery model, so depending on the processing and the backup needs you can select the appropriate recovery model per database. The only exception to this is the TempDB database which has to use the "Simple" recovery model.
Also, the database recovery model can be changed at any time, but this will impact your backup chain, so it is a good practice to issue a full backup after you change your recovery model.
The recovery model can be changed by either using T-SQL or SQL Server Management Studio. Following are examples on how to do this.
Using T-SQL to change to the "Full" recovery for the AdventureWorks database.
ALTER DATABASE AdventureWorks SET RECOVERY FULL GO
Using the SSMS to change the recovery model for the AdventureWorks database.
