Database Files, Recovery Models and Backups Part 2

Yesterday we talked about the .MDF and .LDF files and what their purpose is.  We also talked about the different recovery models available in Microsoft SQL Server and which model to select for your database.

Now, I want to cover database backups.

First, two questions you might ask are:

1. What kind of backups should I do?
2. How often should I do them?

There are three major types of backups in SQL Server:

• Full Backup
• A data backup that contains all data within the given database.  It also contains enough log backup to allow for recovering all data
• Differential Backup
• A backup that contains all data that has changed since the last full or differential backup
• With differential backups, you will be required to restore each backup in order for the data to be in sync.
• Log Backup
• A backup of the transaction log since the last log backup.
• Transaciton log (.LDF file) can be shrunk once a log backup has been performed.  If you find your .LDF file is growing, run a transaction log backup and then shrink your .LDF file (Shrinking the Transaction Log)

For more information, review this Backup Overview

Deciding which backup (or combination of backups) should be selected, ask yourself this: how much data am I willing to lose?  Can you go a full 24 without backing up your data?  Do you need to have as much real time data as you can get?

Most applications will need a backup at least every 24 hours (whether full or differential).  If you can afford to lose 30 minutes to an hour of data, a full backup each day accompanied by a differential backup every 30 minutes might suffice what you need.  If you need a solution that is more real-time then thirty minutes, there are other options that we will not cover in this article (non-Microsoft applications, Replication, and Mirroring [which has been deprecated]).

To read more about backup and restore strategies, go here: Backup and Restore Strategies