Contrasting the different SQL Server recovery models

Those of us just getting our feet wet in the SQL Server pool should know about the different recovery models available as one of the first pieces of knowledge to set in stone.  Since I am approaching this from the aspect of being experienced in the Oracle database environment, I will compare the SQL Server recovery models to their Oracle database analogs where applicable.  Here we go!

Simple Recovery Model – This model is like the Oracle noarchivelog mode.  Transactions performed between backups are lost in the event of a failure and HA features such as mirroring or AlwaysOn cannot be used on a database with this recovery model.  On the plus side, the log file is kept small and tidy.

Full Recovery Model – Like the Oracle archivelog mode, this model will allow you to recover the database to the point of failure.  However, it requires log backups, like archivelog backups in Oracle.  Log backups should be executed in between full backups at an interval based on the amount of activity in the database and the rate of transaction log growth.

Bulk-Logged Recovery Model – The recovery mode is like the Full recovery mode with the exception that it allows for minimal transaction logging during bulk-loading to increase performance during these operations.  Log backups are also required with this recovery model.

More details on the three recovery models can be found here – https://msdn.microsoft.com/en-us/library/ms189275.aspx

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s