Recommendations for Changing the SQL server Recovery Models

A database administrator can change the recovery model of their database at any time as per the requirement. If you have planned to change the recovery model from the simple recovery model to the full recovery model during the bulk operation then be aware that logging of bulk operations will be change from minimal logging to full logging. Here I will tell you some recommendations that are very necessary before or after changing to Simple recovery model.

Recommendations for changing from Simple to Full recovery model: If you are planning to change the recovery model for your database from the simple recovery model to the full recovery model then take care of the followings:

• Take the full or differential database backup of your database immediately after changing the recovery model from the simple to the full recovery model. The log chain will not start until the full or differential database backup.
• Take regular backup of your transaction log and make a proper plan for backing up the log backup according to your database need.

Note: If you don't backup the transaction log on the regular interval then it can expand until it runs out of your disk.

Recommendations for changing from Full to Simple recovery model: If you are planning to change the recovery model for your database from the full recovery model to the simple recovery model then please attention for the following:

• Changing the recovery model from full to simple will break the log backup chain so make a backup of your log backup before changing the recovery model. Log backup will allow you to recover the database up to that point.
• After changing the recovery model, you will need to take the data backup on regular interval to avoid any data loss.

How to change the recovery model: After understanding the impact of backup and restore strategy that will happen after the changing the recovery model to simple, now you can change the recovery model of your database with the help of SSMS. Perform below steps to change the recovery model for your database:

• Connect to the SQL server instance and expand the server tree.
• Now expand the database tree, select user database, or system database that suits your requirement.
• Press right clicks on the database and go-to the database properties.
• Now go-to the select a page pane and click on the options.
• Now go-to the recovery model section and see the current recovery model of your database.
• To change the recovery model, click on the arrow next to the recovery model options and select your desired recovery model: simple, bulk-logged or full.