Understanding the recovery models
Posted by decipherinfosys on May 30, 2008
At many client sites, we have seen that even in the production environment, some of the DBAs/Database Developers run their databases in the Simple Recovery Model – and all these are mission critical databases in which you need zero data loss and a point in time recovery is always needed. When asked why you are running it in the Simple Recovery Model, the answer given is that the transaction log just kept on growing so we changed the recovery model to keep it in check. One needs to understand the different recovery models that are available in SQL Server and also how to use transaction log back-ups in order to keep the growth of the transaction logs in check. Here is a small description of the different recovery models available in SQL Server:
Full Recovery Model: As the name suggests, it is a complete recovery model and allows us to recover all of the data to any point in time as long as all the required backup files that we have are available and usable. All the operations against a database that uses a full recovery model are fully logged. As mentioned above, when you are running under a full recovery model, make sure that you have implemented a transaction log backup scheme as well so that you can keep the log growth in check. Production databases which are mission critical and cannot have any data loss need to be always run in this recovery model. Also, if you are using database mirroring, then you would use this recovery model. When you are using Full recovery model, you can do all types of backups: full backups, differential backups, file/filegroup backups, partial backups, copy only backups as well as transaction log backups.
Bulk Logged Recovery Model: This is a step down from the Full recovery model and as the name suggests, it can be used when we do not want to pay the price of bulk operations (bulk insert, select into…, create index, bcp etc.) taking up space in the transaction log. Databases running under this recovery model do not fully log such bulk operations. So, the transaction log does not get filled up by the bulk operations and we can still do point in time recovery but those bulk operations are lost. So, essentially in environments where one does not do bulk operations, this works the same way as the Full recovery model. And just like the full recovery model, you need to backup your transaction logs else it will keep on growing. All type of backups that can be done with the full recovery model can be done with bulk recovery model as well.
Simple Recovery Model: Simple recovery model is more suited in those scenarios where your data is not that critical and some data loss is acceptable. It allows you to do a simple full backup or a differential backup but not transaction log backups. Any work done since the last backup can be lost. Typically, development or test databases or databases where the data is mostly static or if the data can be re-created again by loading up the data again are scenarios where you might want to use it but never in a mission critical transactional database where point in time recovery is needed. All the backups other than the transaction log backups can be taken for databases that are under this recovery model.
We hope that this gives a very brief but good understanding of the differences between the three recovery models present in SQL Server. You can either change the recovery models from SSMS (SQL Server Management Studio). Right click the database and choose properties and then when you click on options, you will see a drop down for the Recovery model under which you can select which one you want. Alternate way of doing this is through the command line using the ALTER DATABASE command:
ALTER DATABASE <DATABASE_NAME_HERE> SET RECOVERY <FULL OR BULK_LOGGED OR SIMPLE>
Put your database name above (and remove the <> braces and likewise choose your recovery model and remove the <> braces).