Posted by: Denny Cherry
Back To Basics, Recovery Model, SQL Server
The recovery models tell the SQL Server how much transaction log data to keep within the transaction log, and how much data recovery there is. SQL Server has three different recovery models which are SIMPLE, BULK_LOGGED and FULL.
Simple Recovery Model
Simple recovery model is the most basic recovery model and it provides the least amount of protection. The simple recovery model supports only full and differential backups meaning that you can only restore the database to the state that the database was in when either a full backup or differential backup was restored. Typically the simple recovery model is only used for databases like data warehouses or other databases when the data within the database can be reloaded from another source easily. The reason that this is acceptable for data warehouses is because data warehouses are not the production system of record for the data as they are loaded from the production system of record on some schedule, either nightly, weekly, hourly, etc.
Many people that assume that using the snapshot recovery model means that the transaction log isn’t kept. This is not the case. For most transactions the normal transaction log information is written to the transaction log. The different between the simple recovery model and the other recovery models is that with the simple recovery model the SQL Server database doesn’t wait for the transaction log to be backed up before removing the data from the transaction log. Instead it waits for the data pages in the MDF and NDF files to be fully written to disk. Once that happens the SQL Server will mark the rows within the transaction log as being able to be reused.
Full Recovery Model
In the full recovery model the database engine keeps all the transactions within the transaction log file (the LDF file) until they are backed up to a transaction log backup. With the full recovery model because we are taking transaction log backups we are able to restore to any moment in time so long as that moment in time started after the database was created and is before now and that we still have the backups for that period of time.
If for example we take full backups on Monday morning at midnight and transaction log backups every hour at 10 minutes passed the hour, in the event of a database crash we can restore the SQL Server database to any point in time provided that we still have the full backup from the Monday before and all the transaction log backups taken between that full backup up to the transaction log backup which was taken after the problem happened. With this example if we wanted to restore the database to Tuesday afternoon at 1:45:00pm we could do that by restoring the full backup from Monday then restoring the transaction log backups starting at ten minutes after midnight on Monday up through the 2:10:00pm backup on Tuesday. While restoring the final transaction log backup we would simply tell the restore database command that we wanted to stop the restore process at 1:45:00pm on that Tuesday and the restore would simply stop at that point.
Bulk Logged Recovery Model
The bulk logged recovery model is very similar to the full recovery model. Most of the transactions are fully logged within the database’s transaction log. There are a few commands which aren’t such as bulk inserts using the BCP command line tool or the BULK INSERT T-SQL statement as well as a few others. All normal INSERT, UPDATE and DELETE statements are fully logged just like in the full recovery model. The bulk logged recovery model allows for point in time restores just like the full recovery model does. The difference here is that when there is a bulk operation which is bulk logged using the bulk logged recovery model not all the data changes are logged, only the allocation of space within the data files is logged. This presents a problem when the transaction log backup is being taken as the SQL Server needs to backup the records. In order to do this because we don’t have the full transaction log information for these commands the SQL Server database engine simply copies the data pages which were allocated to the transaction log file. Because of this the specific times that the database can be restored to will be limited to times when the bulk operations were not running. So if the bulk operation runs from midnight until 1am every night there wouldn’t be any way to restore to any specific point in time during that one hour window. If you need the ability to restore to specific times within that window and have the data be perfect the full recovery model must be used.