Recovery Models

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Database Administration - Backup & Recovery
SQL Server 2000, 2005, and 2008 support three database recovery models: Full, Bulk-Logged and Simple. Recovery models can be changed through the sp_dboption stored procedure, the ALTER DATABASE command or through the management GUI tools provided with the product (Enterprise Manger or SQL Server Management Studio). The database recovery model determines how much detail is contained in the transaction log. The following table shows the recovery models and types of backups supported by each:

Recovery
Model		 Backup Types
Supported		 Advantages Disadvantages Description
FULL Full
Differential
Trans. Log		 Can recover database up to a certain point in time or up to a marked transaction Transaction log can grow large quickly; Transaction log should be backed up frequently This recovery model logs every statement to the maximum level of detail. CREATE INDEX, bulk-load and SELECT INTO operations are fully logged.
Bulk-Logged Full
Differential
Trans. Log		 Transaction log does not grow as large as with the FULL recovery model. Transaction log can be backed up quicker than with the FULL recovery model Cannot recover database up to a certain point in time or up to a marked transaction. Backing up transaction log that contains bulk logged operations requires access to data files. If data files are not accessible then transaction log backup cannot be taken. Therefore transactions committed since the last transaction log backup will be lost This recovery model performs minimal logging of bulk loads (BCP and BULK INSERT), CREATE INDEX, SELECT INTO and text / image writes. If you have to recover a database with this model you will have to redo operations that are minimally logged (for instance, you may have to rebuild indexes)
Simple Full
Differential		 Transaction log space is kept to a minimum. Bulk-logged operations are not logged. Database cannot be recovered from a transaction log. All transactions committed after the last full or differential backup can be lost. File and filegroup backups are not supported Simple recovery model is appropriate only for non-mission critical applications. You can also use the simple model while database is being developed or tested, since in such environments you can afford data loss.


SQL Server allows changing recovery models without having to backup the database or take it offline. Simply execute the ALTER DATABASE statement as follows: 
/* change pubs recovery model to FULL */  ALTER DATABASE pubs   SET RECOVERY FULL


Or 
/* change pubs recovery model to SIMPLE */  ALTER DATABASE pubs   SET RECOVERY SIMPLE


Alternatively, you could change the database options by executing sp_dboption, which effectively changes the recovery model. For instance, if pubs recovery model is FULL, executing the following statement will change the recovery model to SIMPLE: 
sp_dboption 'pubs', 'trunc. log on chkpt.', 'true'


If the recovery model is set to FULL executing the following statement will change pubs recovery model to bulk-logged: 
sp_dboption 'pubs', 'select into/bulkcopy', 'true'


Although you can change the recovery model without performing any backups, you should consider performing a full backup each time you change the recovery model. In addition, you might have to change your backup strategy if you switch from simple to bulk-logged or full recovery model. After changing the recovery model, you might wish to schedule regular transaction log backups.
Retrieved from "http://sqlserverpedia.com/wiki/Recovery_Models"