As I prepare to speak at SQL Saturday 197 (Omaha Nebraska), I’m listening to Matthew Brimer speak about Database Security & Shrinking Your Attack Surface. It’s an interesting lecture. But since I’m speaking about data recovery plans, I thought I’d post about something along those lines: Recovery Models.
SQL Server has 3 recovery models that are an essential part of any recovery and backup plan: Full, Bulk-Logged, and Simple.
The Full recovery model tracks pretty much every transaction (committed and uncommitted) in the database. If you have Full recovery set on your database, then your Transaction Log needs to be backed up or it will balloon in size as it tracks all inserts, deletes, and updates (which is both an insert and a delete).
The Bulk-Logged recovery model doesn’t track every transaction like the Full model does. Bulk-Logged marks and records the modified database extents (or pages–index or data, depending on the circumstances). This is called Minimal Logging. There is, however, a caveat. Just because the database is set Bulk-Logged, doesn’t mean the whole database is logging the extents / pages. Minimal Logging has prerequisites and if those prereqs aren’t met, then the certain objects are still fully logged.
A table used in Replication cannot be bulk-logged. A table that isn’t table locked (TABLOCK) cannot be bulk-logged. Table “status” also changes how bulk-logging is done. Whether a table is indexed, non-indexed, empty, or loaded can affect what is logged as part of this recovery model. More information can be found by clicking the SQL Server Books Online link. Again, backing up the Transaction Log is essential for preventing run-away log growth.
The last recovery model, Simple, is … well, simple. The Transaction Log isn’t used… Ah, and here we run into a nice little bit of SQL Server myth. Transaction Log backups cannot be run on databases using the Simple Recovery Model. This has led to the belief that SQL Server doesn’t use the Transaction Log for these databases, but that belief is mistaken. Simple recovery does use the Transaction Log, and the Simple recovery databases do use Minimal Logging, just like Bulk-Logged. The major difference between the two is that Simple recovery auto-truncates the Transaction Log, eliminating the need for Transaction Log backups, and that it is only as good as the last data backup. Whereas Bulk-Logged databases can be recovered to the last Transaction Log backup.
Microsoft has a good overview of the Recovery Models online. Understanding these models are an important part of creating a data recovery plan for any server. With these, and a few other business-related tidbits, any DBA can create the best recovery plan (and backup plan) for their business.
If you’re in the Omaha area, stop on by SQL Saturday to hear my presentation.
