SQL SERVER – Simple Recovery Model and Restrictions

SQL SERVER - Simple Recovery Model and Restrictions SimpleRecovery-800x1031 Most of the time during the Comprehensive Database Performance Health Check, I focus on SQL Server Performance Tuning. However, often the client also asks for help when they have a question about SQL Server Engine. Recently I received a question from a client that they want to move their database from the Full Recovery Model to the Simple Recovery Model, can they do it?

The answer to this one is difficult as well as simple. When I asked the reason to my client why they want to move to the Simple Recovery Model, their answer was that they have heard it is better for performance. Honestly, it is not true entirely. I have been doing consulting for over a decade and I have yet to see a single situation when we have changed the recovery model and it the server has performed miraculously well.

The Recovery model is directly connected with the data loss and secondary related to SQL Server features. There are few features of the SQL Server that are not possible when your model is set to Simple. Here is a list of the features which are not supported if you are using the Simple Recovery Model.

  • Log shipping
  • Always On
  • Database mirroring
  • Point-in-time restores

If you are going to use any of the features listed above, they are not supported by the Simple Recovery Model. Additionally, you should make the decision of using the recovery model on your work loss exposure and not on any other factor primarily.

Here are few recent blog posts where I have posted SQL in the Sixty Seconds videos.

Reference: Pinal Dave (https://blog.sqlauthority.com

Exit mobile version