Log Backup Checklist

Backing up your transaction log lets you maintain a record of all the changes to a SQL Server database so that you can restore it later if you need to. The following list will help you remember the key features of log backups so that you can use them to your best advantage.

Use the full or bulk-logged recovery model. If your database is in the simple recovery model, you can't make log backups because SQL Server will truncate your log periodically.

Store your transaction log on a mirrored drive. Even if your data files are damaged and the database is unusable, you can back up the transaction log if the log files and the primary data file are available. Use a RAID level that guarantees redundancy, such as 1 or 10, and you'll be able to back up all the transactions to the point of failure, then restore them to the newly restored database.

Monitor log size. Although carefully planning how large your log should be is vital, don't assume it will never grow bigger than it did during testing. Use SQL Agent Alerts to watch the Performance Monitor counters that track file size, and when the log crosses a threshold that you define, SQL Server Agent can take predetermined actions such as running a script to increase the log size, sending you email, or shrinking the file.

Remember that log backups are non-overlapping. In SQL Server 2000 and 7.0, each log backup contains all transactions since the previous log backup, so a long-running transaction can span multiple log backups. So when you're restoring log backups, don't use the WITH RECOVER option until you've applied the last log—later log backups might contain the continuation of the transactions in the current log backup.

Understand the difference between truncating and shrinking. Backing up the log performs a truncate operation, which makes parts of the log available for overwriting with new log records. This doesn't affect the physical size of the log file—only shrinking can do that.

Carefully plan how often to make log backups. There's no one-size-fits-all answer, and you'll always have trade-offs. The more often you make log backups, the more backups you'll have to manage and restore, but the less likely you'll be to lose transactions in case of a system failure.

The log size doesn't always reflect the log-backup size. If your database is using the bulk-logged recovery model, the log backups will include all data that the bulk operations affected, so the backups can be many times as large as the log file.

Maintain log backups for at least two previous database backups. Usually, when you restore a database, you apply all log backups you made after the database backup. But if a database backup is damaged, you can restore an earlier database backup and apply all the logs made after that backup. For full recovery, you just need to start your restore with a full database backup, then apply an unbroken chain of log backups to that database.

You need log backups to restore from file or filegroup backups. If you're planning to restore from individual files or filegroups, you need log backups from the time the file or filegroup backup was made until the time you restore the backup. The log backups let SQL Server bring the restored file or filegroup into sync with the rest of the database.

To restore to a specific point in time, you need a log backup made in full recovery model. Restoring a log backup to a specific point in time requires that the log contain a sequential record of all changes to the database. If you're using the bulk-logged model and you've performed any bulk-logged operations, the log won't be a complete sequential record of the work, so you can't do point-in-time recovery.

You might need to mix log backups with differential backups. If certain data changes repeatedly, a differential backup will capture only the last version of the data, whereas a log backup will capture every change. Because SQL Server's Database Maintenance Plan Wizard doesn't give options for differential backups, you need to define your own jobs for making differential backups.

Practice recovering a database. Plan a recovery-test day to make sure your team knows exactly what to do in case of a database failure. You may have the best backup strategy in the world, but if you can't use your backups, they're worthless.