SQL Server database backup plan and log truncation

Question

I have a SQL Server 2005 database that is backed up nightly. There backup consists of:

1 FULL backup of the database. 1 backup of the transaction log.

These are currently two separate jobs.

The log is huge and I'd like to set things up so that:

1) the database is backed up in full nightly 2) the log is set such that I can recover the database from any point between one backup and the next.

How can I set this up so that the log files are manageable? I suspect that the log has never been shrunk, as the log is huge.

Answer 1

What you are doing is effectively a SIMPLE mode backup with bonus disadvantage of not shrinking the log. There is no point to back up both at the same time. If you're doing a full backup, you can just truncate the log.

If you're going to be able to restore to any point of time, you will have to do a full backup once a day (say) and back up the log few times during the day. See http://msdn.microsoft.com/en-us/library/ms191429(SQL.90).aspx

Answer 2

You are currently implementing the FULL Recovery Model from the sound of things. This will allow you to restore to a point in time provided that you have a transaction log backup that covers the desired point in time (post full backup).

In order to reduce the size of your required transaction log file, you should look to increase the frequency of your transaction log backups. I would suggest hourly. Once you have gauged the actual usage of your log file, you can then look to shrink it to a more suitable size. The key point to note here is that once a transaction log backup has been completed, the inactive portion of the log file becomes available for use once again. The reason why a transaction log file grows continuously is if the transaction log backups are either, not being taken at all or their frequency is not sufficient.

I would also suggest that you consider performing a mix of DIFFERENTIAL and FULL Backups in order to reduce the collective size of your backed up data. An example schedule would be a weekly FULL Backup, say every Sunday, with daily DIFFERENTIAL backups.

I hope what I have detailed makes sense. Please feel free to contact me directly and I will happily assist you in deploying an appropriate backup strategy for your environment.

Essential References:

1. How to stop the transaction log file from growing enexpectedly
2. Backup and Restoring Databases in SQL Server

Answer 3

One of the things I find with backups is that people typically don't run them frequently enough - especially when it comes to log file backups. And it sounds like you're correct, that the log file isn't being truncated regularly (which means you're likely wasting premium disk space [[1]]). More importantly though, that's leaving you completely exposed from a recoverability standpoint.)

Happily though, getting things up and running as you need them isn't so hard. In fact, I'd recommend the following three videos as they should give you the background info you need, and then the step-by-step instructions you'll want to follow to get everything working correctly: http://www.sqlservervideos.com/video/logging-essentials

http://www.sqlservervideos.com/video/sql2528-log-files

http://www.sqlservervideos.com/video/sqlbackup-best-practices

[1] Maximize Storage Performance: http://www.sqlmag.com/Article/ArticleID/100893/sql_server_100893.html