0

Can you change recovery mode from simple to full with users working on the server?

While the server is changing the mode is the work of the users interrupted? If so, for how long.

Version: SQL server 2016

Thank you very much

2 Answers 2

1

You can - as long as there's no running transaction on that database, or you run the alter statement using WITH ROLLBACK IMMEDIATE. Be careful with this clause as it will rollback nonqualified transactions - see here for a human-readable explanation.

Also, remember to take a full backup after you change the recovery model from simple to full, or the change won't really take place (info here)!

3
  • Does it mean that it gives rollback to all transactions when it is changing mode?
    – felix
    Jul 6, 2020 at 12:09
  • In a nutshell. From the first link: "In case [of] incomplete Transaction ALTER DB … ROLLBACK rollbacks those incomplete transactions immediately, whereas ALTER DB … NO_WAIT will terminate and rollback [its own transaction]."
    – GMassDBA
    Jul 7, 2020 at 16:48
  • I can not cancel any transaction so I will put a task for 4 am to do it for me.
    – felix
    Jul 9, 2020 at 17:48
0

Yes you can. However I do prefer to do this during a maintainence window or during off hours with less load. Best bet to start from all lower env to test and go all the way to prod to see if you find any impacts.

From Microsoft Docs as a note in View or Change the Recovery Model of a Database (SQL Server)

Note! If you switch to the full recovery model during a bulk operation, bulk operations logging changes from minimal logging to full logging, and vice versa

After changing you also might want to revisit the backup strategy and change as per RTO and RPO

Your Answer

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

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