SQL Server – Change Recovery Mode from Simple to Full with Active Users

I would do this when the database is in low activity (end of day, overnight, first thing in the morning). I would NEVER recommend making changes like this to a live database in the middle of working hours. It's probably a good idea to change the database to read-only or make sure no users/applications are hitting it.

• Run a full backup of the database/log file in question, while still in full recovery mode. This will give you a starting point in case anything gets borked and you can just restore it.

• Switch to SIMPLE recovery, I don't like using the GUI, I'd script it out.

Here's a great article from MSDN about things to consider when changing your backup model: Considerations for Switching from the Full or Bulk-Logged Recovery Model.

Merely setting the recovery back to FULL is not enough - you'd need to take at least a differential after setting back to FULL to bridge the gap between the time you changed to SIMPLE and the time you changed back to FULL - then your T-logs should be ok at 6am

Keep in mind that you will not be able to restore to a point in time after the change to SIMPLE and before the differential is taken.

Also, you say that you are taking T-log backups at the top of every hour. So, you are basically saying that it's ok to lose up to 1 hour of data should a disaster occur. Does this actually adhere to your company Recovery Point Objective (RPO)? Please take a look at The 9 Letters That Get DBAs Fired.

And then take a look at Back Up Transaction Logs Every Minute. Yes, Really