Switch from full to simple recovery

Question

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.

Answer 1

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.

Answer 2

Once you switch to Simple the Trans Log will no longer maintain a log of operations since your last (database files) backup - at the point of the switch all transactions will have been committed to the main database files so will not be discarded - they are already effective in the database files.

Your point 1 is not (in my opinion) worth doing - once switched to SIMPLE, you should backup the Database files - which will contain all data.

Remember that the FULL recovery model only exists so that you don't have to backup the database files every time (so in FULL recovery model your recovery process is: Restore Main Database files, then Restore Transaction Log Backups). With the SIMPLE model the recovery process is only Restore Main Database files.

What I'm trying to say is: As quickly as possible (depending on performance and the type of transaction) the transactions that can be found in the Transaction Log are Committed to the Main Data files (mdf, ndf) which are stored in the .bak file when Backed up. So SIMPLE model allows you to restore to the point in time when you took a .bak FULL model allows you to restore to the point in time when you last took a .trn (transaction log backup) - in the case of multiple trn backups that would be a restore of Bak + trn + trn + trn (if you had taken 3 trn backups since your last bak).

The only purpose of the differing recovery models is backup strategy and time saved by not taking a bak every time.

Now I've typed it out, I see why you've asked the question!

Although not perfect English, I think this gives a relatively good breakdown of the different models and why you'd use them: http://www.todo-backup.com/backup-resource/sql-backup-software/three-sql-server-recovery-models.htm

This is how I would do your list:

1. Switch to simple.
2. Perform full backup of data files (.bak)
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. Perform a full backup.
6. Switch to full (I assume you're switching back!??!)

Notes: If you're worried, then do a Backup before 1 as well. I'll leave 3 and 4 as they are, because they're a whole different question! (http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/)