SQL Server Recovery Models

In our previous article we discussed how to design a backup strategy.  This time we’re going to talk more about how to implement the different recovery models you’ll need to accomplish your goals.  In SQL Server 2008 there are 3 recovery models you can use: simple, bulk-logged, and full.  These recovery models really just define how log management behaves so you can have what you need to perform your desired backups.  And while the different modes define what you can and can’t do with your backups, the focus is really on recovery.  This is why they’re called recovery models, because you’re really defining the level of data recovery you need and SQL Server 2008 will manage the log accordingly.  Let’s go ahead and discuss each one so you’ll know what they do.  I’ll discuss how to actually switch between the modes towards the end.

Simple Mode

Putting a database into simple mode means that you don’t want to take log backups and all you want to do is rely on full backups for your recovery.  How it accomplishes this is simple.  It forces the log to truncate when the database writes data to the disk (we call that a checkpoint).  So if the log isn’t storing any long-term data, then it can’t be backed up.  It’s also worth noting that SQL Server 2008 won’t shrink the log file itself; it only deletes the inactive portion of the log.  This means that if you have a huge transaction that grows your log to 50GB, and you’re in simple mode, the log will be 100% full at this point.  However, when the operation completes, that portion of the log is marked as inactive and is deleted, but the file itself remains 50GB.  Only now the file is mostly empty.  For more on managing database files see:  How to manage SQL Server database files.

Full Mode

This mode gives you everything.  It allows you the fullest set of backup options which in turn gives you the fullest set of recovery options.  In this mode everything is fully logged so you can always recover individual transactions.  The log is never truncated by the system after a checkpoint which means that you have to manage your log files somewhat manually.  OK, it’s not really manual in the sense that you have to physically do it, but here the log truncation switches from checkpoint (an automatic system process) to the log backup (a user-defined process).  So the log is only truncated when you backup the log.  Again however, the log file itself isn’t shrunk, the inactive portion is just deleted.

I’m going to go ahead and dispel a myth real quick.  In earlier versions of SQL Server you used to be able to truncate the log yourself with the command ‘backup log databaseName with truncate_only’.  And while this command won’t fail, it doesn’t do anything anymore.  The command just gets ignored.  Currently there are only 2 ways to truncate the log while in full mode.  The first, as I already mentioned, is to take a log backup.  Clearly once the log has been backed up it is no longer needed by the system.  The second way to truncate the log is by switching the database to simple mode.  So if you don’t want to save any of the log you’ve amassed by taking a backup, then your only other option is to set the database to simple mode which will assume that you don’t want log backups and truncate the log for you.  And again, this process doesn’t shrink the file itself.

Bulk Mode

I saved this one for last because it’s really just an adjunct to the full mode.  In fact, it is full mode with one difference; bulk load operations are minimally-logged.  The log backup and truncation methods apply here just as they did in full mode so nothing really changes.  This isn’t a mode you’ll actually run your database in though.  If you have a need to backup your logs for full recovery, you’ll run in full mode, and only switch to this mode when you need to do large bulk load operations.  Switching to bulk mode for these operations allows you to minimally log them to not only greatly reduce the size of the log, but also to speed the operation.

So if it’s starting to sound like there’s a difference in the recovery capabilities between bulk and full mode then you’re paying attention.  In bulk mode you lose the ability to do point in time recovery (PiT).  This means that as you’re restoring logs (in full mode) you have the ability to tell it to only restore up to say 2:00pm.  This was the last known good data you had and you don’t want to restore any corruption that occurred after that.  Bulk mode doesn’t always mean that you’ll lose PiT, but you should count on it since you only have limited control over when SQL Server 2008 kicks an operation into bulk load mode.

Switching between modes

As you would expect, you can easily switch between any of the modes without taking the database offline, or interrupting operations.  It’s just a matter of a simple metadata change in the database properties.  To do this, you simply open a query window in SSMS and type the following command:

ALTER DATABASE databaseName

SET RECOVERY Simple | Full | Bulk_logged

 

So to set your database named ‘HRCopy’ to simple mode you’d type this:

 

 

And again, the editor doesn’t care about whitespace or case so you could type it all on a single in small letters if you like.

Switching modes (specifically to simple mode), as I mentioned before is the only way  to truncate the log (outside of a log backup).  However, you should really be aware that there are consequences to switching between modes.  For instance, if you’re in full mode and you want to truncate the log by switching to simple mode you’re free to do that.  However, if you want to switch back to full mode and start taking log backups again, you’ll have to take a full backup before you can do that.  In fact, even if you’re in full recovery mode, your database is effectively in simple recovery mode if you don’t have an initial full backup.

This has been a pretty good explanation of the recovery models in SQL Server.  And while you could read further to get a better understanding, you can also go far just by understanding the concepts we’ve outlined here.