5

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.

1

2 Answers 2

2

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.

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/)

3
  • I appreciate that the switch to simple recovery model persists the data to the mdf, ndf files. But should i need to restore to a point in time between the last scheduled trans log backup and the first manual full back up post recovery model switch, would I be able to do so? I don't think I would.
    – Steve
    Apr 15, 2015 at 10:26
  • If you're asking whether you'd be able to restore up to the point in time just before you changed to Simple (i.e. while it was still FULL) - no you wouldn't, in this case you are right, you should run a Transaction Log backup before the switch as you stated. Not sure I'd ever use this, as the Backup at point 2 of my list is 1 file to restore (rather than bak, trn, trn, etc.), but I appreciate that this would restore a SIMPLE model database. As with most things when working with SQL, caution is the best method. - there nothing to stop you taking TRN backup pre point 1, at point 2, after 3, etc!
    – MHSQLDBA
    Apr 15, 2015 at 10:51
  • FULL model allows you to restore to the point in time when you last took a .trn (transaction log backup) -- The only purpose of the differing recovery models is backup strategy and time saved by not taking a bak every time. It's important to note that transaction log backups allow you to restore to any point in time up to that of the .trn backup (RESTORE ... WITH STOPAT...), (unless you were in BULK_LOGGED during the time covered by the log backup)
    – Mark Sowul
    Sep 13 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.