Recovery models

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

For each database that you create in SQL Server, with the exception of the system databases, you can configure it to use one of three possible recovery models (simple, full, bulk-logged).  Here is a simple script to display the recovery models of all on-line databases:

SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

If you are running SQL Server 2005 or later, you can use this script instead:

SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

This is how you can change a database's recovery model to the simple recovery model:


to the full recovery model:


and to the bulk-logged recovery model:


Which recovery model do you need to use?  It depends on how much data you can afford to lose.  Let's use the following diagram to illustrate the difference between the recovery models, where a full database backup is performed at 9:00 a.m, and 11 a.m.


The simple recovery model

Assume that there was a hardware failure at 10:45  a.m.  If the database was using the simple recovery model, you would have lost 105 minutes of work.  The latest point at which you can recover the database to is 9:00 a.m, since that was the time the last full backup that was made.  You could schedule differential backups to run periodically e.g.


In this case, you would lose 45 minutes of work.  Now, assuming that a user deleted a critical table at 9:50 a.m.  Can you recover to the point in time just before the deletion?  No.  The differential backup contains only the changed data pages.  It cannot be used to restore to a specific point in time.  You'll have to restore the database to its 9 a.m state, and redo 49 minutes of work.  Then, you'll also need to redo the work that was performed after the deletion up to the time the error was discovered.

The full recovery model

If no transaction log backups are made between 9 a.m and 11 a.m, you would face the same situation as you would if the database had been using the simple recovery model.  In addition, your transaction log file would be much larger, as SQL Server will not truncate committed and checkpointed transactions until they have been backed up.

Assume that the transaction log is backed up every 30 minutes:


If a hardware failure occurs at 10:45 a.m, you would lose 15 minutes of work.  You can use the full database backup at 9 a.m, and apply the transaction logs to bring the database to its state at 10:30 a.m.  What if some critical data was deleted at 9:50 a.m?  Using the transaction log backup that was made at 10:00 a.m, you can restore the database to its state at 9:49 a.m.  You would still need to redo the work that was performed between the time of the deletion up to the time the error was discovered, as you cannot restore up to 9:49 a.m, skip the 9:50 a.m transactions, and restore the later transactions.  Admittedly, that would be great.

There are tools in the market that can simply reverse the changes created by user errors, while keeping the other transactions intact.  You still need transaction log backups to do that if the transaction is no longer in the active transaction log i.e. it has been backed up.

The bulk-logged recovery model

The situation with the bulk-logged recovery model is identical to the full recovery model if no minimally logged transactions are created in the database.  An example of a minimally logged transaction is a SELECT INTO operation.  Say that such a transaction occurred at 9:40 a.m.


This transaction would be minimally logged, which means that SQL Server only records the changed database pages arising from the transaction.  It does not log every insert into the new table.  Again, say that a critical table was deleted at 9:50 a.m.  What happens now?  You can no longer restore the database to its state at 9:49 a.m, as the transaction log backup created at 10:00 a.m cannot be used to restore to a specific point in time.  The best you can do is to restore the database to its state at 9:30 a.m.  Whenever a transaction log backup contains one or more minimally logged transactions, you cannot use that backup to perform a point in time restore.


Why then would anyone use the bulk-logged recovery model?  The main reason is performance.  Let's use the example of a SELECT INTO operation, say creating a large table from the results of a query.  If using the full recovery model, the details of every insertion into the new table is logged, and the transaction log will consume more space.  If using the bulk-logged recovery model, only details of the modified data pages are logged, allowing for better performance.  As we have seen, this comes at the expense of being able to perform a point in time restore using the transaction log.

The operations that are minimally logged are as follows (taken from SQL Server 2005 Books Online):

·Bulk import operations (bcp, INSERT ... SELECT * FROM OPENROWSET(BULK...), and BULK INSERT).

·text, ntext, and image operations using the WRITETEXT and UPDATETEXT statements when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

·SELECT INTO operations.

·Partial updates to large value data types using the UPDATE statement's .WRITE clauses when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

·If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged whether the operation is executed offline or online.

·DROP INDEX new heap rebuild (if applicable).

See Books Online for more details (Bulk-Logged Recovery [SQL2000], Backup Under the Bulk-Logged Recovery Model  [SQL2005]).

When a database's data files are unavailable, perhaps due to a hardware failure, you can still back up the transaction log if the media it is on is still available.  However, you need to issue the BACKUP LOG command with the NO_TRUNCATE option.  This will allow you to take a backup of the transaction log up to the point just before the hardware failure, and is usually referred to as backing up the tail of the transaction log.

However, if your database is using the bulk-logged recovery model, and the transaction log contains minimally logged transactions, the data files which contain the modified pages must also be available.  If those data files are unavailable, you will not be able to back up the tail of the transaction log.  This is another point to consider when using the bulk-logged recovery model.


In summary, the simple recovery model provides the least number of options for recovery, but is the simplest to manage.  The full recovery model allows for the most flexibility when it comes to restoring databases. The bulk-logged recovery model sacrifices some of those flexibility, in return for better performance for bulk operations.  See Books Online for a more detailed comparison of the recovery models (Selecting a Recovery Model [SQL2000, SQL2005]).

If you have a new database that's running a full or bulk-logged recovery model, but have never made a full database backup of that database, the transaction log will be truncated every time a checkpoint occurs.  This behaviour is similar to when the database is running the simple recovery model.  So if you have a database that's running the full recovery model and you find that the transaction log never grows even though you have not backed it up, you need to check if you have even made a full backup of that database yet.

See also:

·Using bulk-logged recovery model but transaction log backup is still large

·Backup and restore history details

Document history
9/19/2010    Added notes on new database's state.    
7/23/2008    Added details - transaction log tail.    
6/27/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.