Important Points When switch from one recovery model to another

You can change from one recovery model(simple, bulk logged, full) to another recovery model at any time. You can easily view & change recovery model by using T-SQL command. First Connect to the Database Engine, after that Use following query to view the recovery model:


 




SELECT name, recovery_model_desc

FROM sys.databases

WHERE name = 'model' ;

GO


Use following query to change recovery model:


USE master ;

ALTER DATABASE model SET RECOVERY FULL ;


But there are some points which you have to remember before & after changing the recovery model:


Before Changing the recovery models:


Full Recovery Model to Bulk-Logged Recovery Model- NO Action Required

Full Recovery Model to Simple Recovery Model- Backup the transaction-log

Bulk-Logged Model to Full Recovery Model- NO Action Required

Bulk-Logged Model to Simple Recovery Model- Backup the transaction-log

Simple Recovery Model to Full Recovery Model- NO Action Required

Simple Recovery Model to Bulk-Logged Recovery Model-NO Action Required


After Changing the recovery models:


After switching from the bulk-logged recovery model to the full recovery model, perform the log backup.


After switching from the simple recovery model to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.


After switching from the simple recovery model schedule regular log backups & also update your restore plan as per your requirements.


After switching to the simple recovery model, you should disable any scheduled transaction log backups because it is not possible under simple recovery model.

After switching to the simple recovery model, first make sure that periodic database backups are scheduled. Backing up your database is necessary to protect database as well as to truncate the inactive portion of the transaction log.

 

These points will help you to change recovery models without data loss. Any advice & Comments are appreciated!