Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Recovery Models



By:
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.

sql server recovery model
Additional Information

Last Update: 2/22/2019




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools