Database Recovery Models

Database recovery is a very important thing, especially to DBA’s, and the occasional developer who screws something up.  Please note, I am a developer and I would NEVER mess ANYTHING up in a database.  I treat all databases equally and with respect.  Anyway, we are getting off track.  There are three recovery models: Simple, Full and Bulk-Logged.

Simple:  This method does not do log backups.  This causes an issue on systems where if a failure occurs then recent data needs to be recovered.  If you are only loading something like a data warehouse once a day then this may be a good option as you don’t have new transactions writing to the database all day.  You only care about the data at the single point in time of loading.  Any changes in the database since the last backup will be lost.

Full:  This method allows you performs log backups.  This method will allow a point in time restore of your environment.  All transactions in the logs are kept until a transaction log backup is performed.  Individual data pages can also be restored under this method. Also note that the only thing that will clear the transaction logs are transaction log backups. Here is a link to Paul Randal’s blog on backup myths.

Bulk-Logged:  Logs are backed up here as well.  Here if the log gets damaged or a bulk-logged operation happens between the point of last backup and disaster all those operations must be redone.  If there are no bulk-logged operations since the last backup then no data needs to be reloaded.  Note that point in time recovery is NOT supported with this method.

Knowing the difference between these options is critical.  There comes a time when you will need to know what model your databases are using.  Luckily there are several ways to do this, the one in particular that I will show is how to get all databases recovery model they are currently set up with.

SELECT
recovery_model_desc AS RecoveryModel
,name AS DatabaseName
FROM sys.databases
ORDER BY DatabaseName

You can also programmatically change the model of a database by using ALTER DATABASE as shown below.

ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE

The syntax for the three RECOVERY model options: SIMPLE, FULL OR BULK_LOGGED

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

Leave a Reply