How to Switch from Full to Simple Recovery in SQL Server

backuprecoverysql server

This is a theoretical question to help my understanding. What is the process to switch from full to simple recovery model in sql server?

Is the process as follows:

  1. Take a trans log backup before the switch to simple so I can do
    point in time recovery up until the switch.
  2. Switch to simple.
  3. Possibly run DBCC SHRINKFILE with TRUNCATE ONLY to free up some disk
    space.
  4. Possibly alter the database to decrease the trans log file size.
  5. Preform a full backup.

I guess my main questions about point 1. If I don't take a trans log backup will SIMPLE discard any data in the final trans log or does switching to SIMPLE backup the trans log (i don't think it does).

Also I believe that it is best practice to perform a full backup post switch to SIMPLE so new transactions can be pushed from the trans logs to the data files at check point.

Best Answer

IMHO you MUST take transaction log backup before swithching to simple recovery it would help you in PIT recovery as well as its documented and recommended in BOL Article. It wont do any harm and you will have a failsafe.

Please also note its advisable to change recovery model when load on database is relatively less. Although you can change it in peak hour but changing recovery model does takes lock on database and if load is high you might face delay.

No transactions/changes would be lost, if you change from full recovery to simple. Changing recovery model would force a checkpoint which would commit transactions which can be committed. After changing recovery model to simple automatic checkpoint would truncate transaction logs(if no long running transaction is holding the logs)

Point 3 and 4 are totally not required, do you have specific reason to do it ?

Point 5 is necessary and you must at least take full backup daily of database in simple recovery. However backup of database should be according to RPO and RTO agreed. You can also take differential backup in simple recovery to reduce RTO.