In this post, we will take the path of understanding what a recovery model is and how it relates to your database, and why it is important.

We will show you how to find out what is the current recovery model of your databases and how to change them.

What is a recovery model?

A recovery model is a database configuration option that you choose when creating a new database, an option that determines whether or not you need to back up your transaction log, how transaction activity is logged, and whether or not you can perform more granular restore types that are suitable for audits, such as file and page restores.

For more information on what the recovery models are for databases, please review our older article about this, here.

How to change the recovery model

Before you switch recovery models, from FULL RECOVERY or BULK-LOGGED you should back up the transaction log.

Also, be aware that the point-in-time recovery is not available for the bulk-logged recovery model.

Thus, you might have to do a restore of the transaction log if you use the bulk-logged model and create transactions, otherwise, you might experience data loss.

In order to be protected as much as possible in case of a disaster situation it is recommended that you switch to a  bulk-logged recovery model, but only under the following conditions:

• Users are not allowed access to the database
• All of the changes that are made while under the bulk mode can be recovered without being dependant on log backups.

If these conditions are met then you are protected against data loss while restoring a transaction log that was backed up under the bulk-logged recovery.

In case you make the change to FULL RECOVERY while a bulk operation is ongoing, the logging of the bulk operation will switch from minimal logging to full logging. The same rule applies backward.

In terms of security, you will need to have permission to run ALTER queries.

Using SSMS

• The first step is to connect to the correct SQL Server instance and in the Databases folder right-click the database for which you want to review/modify the recovery model and select Properties.
  
• On the window that appeared select on the left side the Options page and then you can see, as marked in the image below, what is the current recovery model for your database and, the drop-down menu from where you can switch to a different recovery model.
  

Using T-SQL

• In order to view the recovery model of your database using queries, you can use the prototype query below:

  SELECT name, recovery_model_desc
  FROM sys.databases
  WHERE name = 'myFirstDatabase'

• In order to change the recovery model of your database using queries, you can use a query based on the one below:

  ALTER DATABASE myFirstDatabase SET RECOVERY FULL;