RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server Sanity: Maintain Healthy Transaction Logs for Easy Data Recovery : Page 3

Learn how executing frequent backups and maintaining healthy transaction logs in SQL Server leads to quick recovery. Failing to do so can mean having to re-enter a lot of data.

Back It Up
SQL Server offers simple, full, and bulk-logged recovery models. For the most part, you should choose full, which allows you to back up both the database and the transaction log. You can back up a transaction log quickly and frequently; every few minutes isn't too often if data is critical.

If the worst happens, back up the current transaction log first. Then, restore the last full database backup, and all subsequent transaction log backups. For instance, suppose you adhere to the following backup schedule and a failure occurs at 9:00 PM:

8:00 AM Back up database
10:00 AM Back up transaction log
12:00 PM Back up database
2:00 PM Back up transaction log
4:00 PM Back up transaction log
6:00 PM Back up database
8:00 PM Back up transaction log

First, you'd back up the 8:00 PM transaction log. Then, you'd restore the database using the last database backup from 6:00 PM. Finally, you'd apply the 8:00 PM transaction log backup and the active transaction log. (Differential backups are a bit more complex.)

After backing up a transaction log, SQL Server truncates the log's inactive section and reuses it to store new transactions. That way, the log doesn't grow uncontrollably large. Remember, SQL Server doesn't use the inactive items during recovery because those transactions are already complete.

If possible, don't store a database and its backup and transaction logs on the same server. Store these files on different physical disks, ideally located in different buildings.

Warning: Simple Recovery May Not Be Enough
Some experts suggest using the simple recovery model because SQL Server truncates the transaction log at every checkpoint, which keeps the transaction log at a manageable size. If you follow this advice, however, you'll be living on the wild side. In a crash, you'll lose everything up to the last backup because the simple recovery model offers no transaction log with which to restore from the last backup to the crash. So be sure to back up the database frequently if you opt for simple recovery.

On the other hand, if you seldom change data, or change only a few items frequently, simple can be more efficient, but that's really the only good reason to consider it. Otherwise, the full model is the way to go.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date