SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Log Backups

While presenting a session on Common Backup Problems both at SQL Saturday in Orange County and at SQL Connections in Las Vegas, questions came up regarding whether, when, and how your databases should be configured in FULL recovery mode and what that means for your recovery plans.

To boil the questions down to as simple a single statement as possible:

Should your database be in FULL recovery mode at all times which requires you to run log backups on a regular schedule?

The same question came up while Brad McGehee(blog|twitter) was presenting a session on using the settings on your SQL Server instance and your database to ensure you got proper performance. We both answered the question the same way, but our emphasis was slightly different.

Brad’s answer was that, yes, you should, by default, set your databases to FULL recovery in order to plan for the possibility of a point in time recovery. I agree with him.

But, my answer was different. What I said was, you need make a choice here. Yes, you should have your production databases in FULL recovery in order to arrive at the best possible recovery process. However, that means you’re dedicating yourself to maintaining log backups. If, for whatever reason, you can’t maintain log backups, then you should set your recovery to SIMPLE.

My point was not to disagree with Brad, or, more accurately, I don’t think he disagreed with me (since he went on 2nd). Rather, I’m trying to tell you that you must make that choice. You need to either commit to the process of setting up an appropriate recovery process for your production systems, which, in the majority of circumstances should include log backups, or, you need to admit that you can only recover your databases to the last FULL or DIFFERENTIAL backup and make that part of your documented plan, setting your databases to SIMPLE. What you can’t do, at least not any more since SQL Server 2008 was released, is rely on BACKUP LOG WITH NO_LOG or WITH TRUNCATE_ONLY as a means for leaving your database in FULL recovery, but also throwing away the log data (and I’ve never understood why people do that except through ignorance of how Log backups & recovery works).

Now, you have to commit. I believe you should set up the Log backups, but if you don’t, fine, turn off FULL recovery. Just do so with the complete knowledge of what that means.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...