What is Simple Recovery Model and When You Should Use it in SQL Server

In this article, we look at a simple recovery model which incidentally does not include log backups.

A Full Recovery Model which does not make use of log backups is called a Simple Recovery Model. A Simple Recovery Model forms the most basic recovery model available in SQL Server. All the recovery models that SQL allows the users access to, are based on maintenance of transaction log, the feature in the application that decides how the transactions are logged, is the Recovery Model Feature.

Simple Recovery Model

Simple Recovery ModelThis is the only recovery model in SQL Server that does not make use of transaction log backups. Simple recovery model does circular logging. Meaning, once you have reached the end of the transaction log, it will begin again from the beginning of the transaction log, to make use of all the left – over space. In this model, very little part of the information in transaction log remains, every time the database reaches the end of the log, the application truncates the log, thus leaving no log entries to be used for the purpose of data recovery in the time of a disaster.

When using a simple recovery model, you cannot back up data to the exact given point of time, but only till the last Full or Differential backup. This happens because there is no use of transactional log backups in this model. If you are using this model, there is no scope of recovering data that was created after the differential or full backups. There is least amount of administration that is needed in this model of recovery, as compared to other models available.

When Can You Make Use of Simple Recovery Model

SQL Server Recovery ModelSimple Recovery Model is best suited for situations where it is not important for the user to restore all the latest changes made in a database after a backup, or where there are no frequent changes made in a database, for example an old or not much used database. It is ideal for non – transactional databases, where the user can afford to lose some amount of data. Simple Recovery Model is not ideal for situations where the user cannot afford to lose data, if you want to use a recovery model where no amount of data is lost, then this model should NOT be used.

Another situation where one can use this model is, when the user does not want the backup to take up too much of space, and instead wants to save space. This model makes use of the unused space in transaction log, thus making use of all the remaining space, instead of using more space. However it is important to note, if you accidentally delete data while using this model, you will not be able to retrieve it. Also, in-case of corruption in databases, you will lose all data and will not be able to restore it. In case you are using this recovery model always keep a repair sql tool handy to deal with contingencies.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair access and sql recovery software products. For more information visit www.datanumen.com

Comments are closed.