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.
- 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 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.
- 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;