When you create a new database and prepare the environment for a certain application to be deployed, you must take into consideration the database recovery model configuration. Each new database created will have the same Recovery model set to model system database. model database works as a template for each new created database. The default setting is “Full”.
The first thing to do is to decide whether you need Full, Simple or Bulk-Logged Recovery Model on your databases. I will not enter in many details about what each model means as there a lot of very good articles describing the pros and cons for each (links to them are posted at the bottom on this post).
I will mainly focus on recovery procedures in specific types of recovery models. They are different and there are some factors that need to be considered before you decide which model to set on your databases.
In Full recovery model all transactions are logged and you have full control over your business – human or technical mistakes are not longer a disaster. So, you can recover the database to the last transactional backup log done – be it 30 minutes or 10 minutes ago – depending on your schedules.
In order to be able to do so: Full backups, Differential backups (if you want to reduce the recovery process to a smaller backup chain) and Log backups must be enabled according to specific schedules that best fit your transaction flows. The transaction log needs to be backed up regularly to prevent not only the loss of work but also to reduce the transaction log size.
- Recovering the last Full backup + each one of the Log backups performed since the last Full Backup.
- Recovering the the last Full backup + last Differential made after last Full + each one of the Log backups performed after the last Differential Backup.
- Alternatively if the database is damaged, corrupted and has entered in suspect state you can recover the database to the last transaction from the current transaction log (not-backed up). This can be done by backing up the tail-of-the-log if possible and try applying the 1′st or 2′nd of the above methods + the tail-of-the-log just backed – up. (see Database Recovery procedure – Data File deleted (Part 2)
Conclusion: Stick to the Full recovery option if your business is critical and transactions can’t be re-created or restored easily from other tools.
I will keep it simple. Choose this model if your databases are for DEV/Test purposes or…. are Production but not critical ones such as: databases with static data, with data that be re-created or recovered from other tools and mostly databases containing read-only data (data warehouse databases).
You must be sure that your system can leave with a loss of all transactions processed since the last Full or Differential was made. Although Log backups are not possible, I suggest you enable anyway more frequent differentials as these can be really useful for lost wide developments.
- Recovering the last Full backup + last differential since the last Full Backup.
It is quite similar to Full recovery model, with the single difference that in Bulk-Logged recovery model NOT all transactions are fully logged and you CAN’T have full control over your business. Transactions that are minimally logged are: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations are not recoverable. You still need to do full, differential and transaction log backups.
If a disaster forces you to recover a database set in Bulk-Logged recovery model you will able to do so by:
- Recovering the last Full backup + last Differential made after last Full + each one of the Log backups performed since the last Full Backup that DO NOT contain any bulk- logged transaction. In other words, you will be able to recover completely the database to the last log backup made only if it’s not containing bulk activity since the last Full backup made. If there was, you will be able to recover the database using log backups only to the beginning of that log backup which has bulk activity.
- Contrary to Full recovery model there are exceptions when you can backup the tail-of-log in order to completely recover the database to the last transaction made.
- If there has been a bulk-log operation performed in the BULK-LOGGED recovery model since the last log backup made – in that case a tail-of-the-log backup is not possible at all, and you’ll have lost all transaction log generated since the last log backup.
- If you are lucky and there are no bulk-logged operations performed in the Bulk-Logged recovery model since the last log backup made – in that case a tail-of-the-log backup is possible, but if there are other bulk-logged operations in some of the previous log backups since the last Full - the tail-of-log can’t be used in a sequential restore.
- You can backup the tail-of-log and use it only if there are no bulk-logged operations caught in any of the needed previous log backups and non in the current not-backed up transaction log.
Conclusion: Why you should use Bulk-Next Logged recovery model? If you have a big amount of bulk insert operations that are scheduled at specific hours (nightly) the bulk-logged recovery model may be the best choice. Do a Full backup after the Bulk insert and you will have a good backup log chain to recover from until the next bulk operation.
I heard many times people complaining that their database log file is growing huge even if the database is in Simple recovery model. It is absolutely normal because the SQL transaction mechanism (ACID) doesn’t change when recovery model is changed. In order to keep a transaction atomic transaction log will be used no matter which recovery model you use. Atomicity refers to the idea that a transaction needs to be processed completely or not at all. In other words, if a transaction fails at any point in the process, the entire transaction must be rolled back and the log is the place where all modifications are recorded.
At each DML statement (INSERT / DELETE / UPDATE) the transaction log will be used. How? At each data modification statement, SQL will read the data pages affected by the modification into buffer cache, updates with new values also in Buffer cache and records the modification into the Log file. This is why the Log is used and you can see big Log growth during a transaction.
In Simple recovery model – when a Checkpoint occurs (usually an SQL internally managed operation) – the dirty data pages (which were modified in buffer cache) are written (flushed) to disk. Also at checkpoint the inactive part (not used by any transaction) of the log is truncated making the Transaction log re-usable for other transactions. After the log was truncated, the Log file than can be shrinked in order to reduce the physical size if needed.
In Full recovery model, the log is truncated only after a log backup. The checkpoint does not truncate the inactive part of the log file. This is the main difference between Full and Simple recovery mode. A good start in understanding better all this can serve the following articles: