How to change the recovery model on a database

change the recovery model on a database
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 which you choose when creating a new database, option which 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 audit, 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 on 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 which 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 backwards.

In terms of security, you will need to have permissions 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.

change the recovery model

    • On the window that appeared select in 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.

change the 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;

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

Leave a Reply

Your email address will not be published. Required fields are marked *