Data Centers

Three recovery models for backing up your SQL Server

Backing up SQL Server is a standard practice, but knowing which recovery model to use can be confusing. Use these tips to get started with your backup strategy.

By Ed Martin

With SQL Server 2000, there are myriad choices for backing up your database. Whether your database is small and changes infrequently or very large and changes rapidly, there is a backup strategy to meet your needs. Let's take a look at some of the basic backup strategies that will work for a variety of environments.

This article assumes that you are authorized to run backups for your database. That is, you are either the system administrator or in the db_owner or db_backupadministrator roles. Also, it assumes you have the operating system privileges to utilize the necessary resources required for a backup, for example, access to disk or tape drives.

Where to start
Before you begin backing up a SQL Server database, you need to know which recovery model the database is using. There are three different recovery models: FULL, BULK_LOGGED, and SIMPLE.

The FULL recovery model gives you the most recovery flexibility. It's the default recovery option for new databases. This model allows you to restore just part of a database or do a complete recovery. Assuming the transactions logs haven't been damaged, you can also recover up to the last committed transaction prior to a failure. This method uses the most transaction log space of all the recovery models and it causes a slight hit to SQL Server performance.

The BULK_LOGGED recovery model has fewer recovery options than the FULL model, but it doesn't have as severe a performance hit on bulk operations. It uses less log space on certain bulk operations because it records only the operations' results. With this model, however, you can't restore to a specific mark in the database, nor can you restore just parts of the database.

The SIMPLE recovery model is the easiest of the three to implement and it uses the least amount of storage space. However, recovery is limited to when the database was last backed up.

To find out which recovery model your database is using, run the following command, which should return either FULL, BULK_LOGGED, or SIMPLE:
SELECT dbpropertyex("database", "recovery")

To change the recovery option for a database, run this command:
ALTER DATABASE database name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED}

In addition to data, SQL Server backups also contain the database schema and database metadata (e.g., database files, file groups, and their locations). SQL Server allows users to remain on the database while the backup occurs, so any transactions committed during the backup are recorded as part of the backup.

Backup the database
To back up a database, you can use the BACKUP command. (As an alternative, you could use SQL Enterprise Manager.) As always, it's a good idea to know the syntax of the command first. The BACKUP command has many options, but the basic syntax of the command is:
BACKUP DATABASE { database_name }
TO < backup_device > |


A backup_device can be a disk or tape—or it might be a logical backup device representing a disk file, a tape, or a named pipe.

If you're looking to do a quick, one-time backup, use a disk file like so:
BACKUP DATABASE Northwind TO DISK = "c:\backup\Northwind.bak"

Alternatively, if you wish to back up to another server, UNC names can be used:
BACKUP DATABASE Northwind TO DISK = "\\FILESERVER\Shared\Backup\Northwind.bak"

For regularly scheduled backups, a logical backup device should be used. A logical backup device can store several database backups and can reside on disk, tape, or a named pipe. If you're using a tape device, the tape drive must reside on the same physical server. Named pipes can take advantage of third-party backup software.

To create a logical backup device, use the sp_addumpdevice system stored procedure. Again, SQL Enterprise Manager can be used to create the backup device. The command line syntax is shown here, in Listing A.

Listing B offers an example of creating a logical backup device on disk.

Using the previously created backup device, the Northwind database could be backed up using this command:
BACKUP DATABASE Northwind TO DiskBackup

Large, frequent backups
At this point, I've demonstrated how to backup an entire database. However, this only allows you to recover data up to the time the backup was completed. If the database changes frequently and is large, frequent full database backups could be impractical due to time and space constraints. There could be significant data loss in the event of a database failure.

There are two ways of improving recoverability in such cases, both of which require a full database backup. Both methods also require the database recovery model to be either FULL or BULK_LOGGED.

The first method is with a differential database backup, which captures and stores only data that has changed since the full backup. With its smaller file and concise information, it's very fast when it comes to data recovery.

This example creates a differential backup on a logical backup device called DiffBackupDevice:
BACKUP DATABASE Northwind TO DiffBackupDevice WITH DIFFERENTIAL

The second method for improving recoverability is with transaction log backups, where recovery can be done to a specific point in time.

You may be asking how this is possible. Remember that the purpose of a transaction log is to record all transactions that occur within a database. A transaction log is what allows COMMIT and ROLLBACK to work correctly. To achieve this functionality, a before and after picture of the data must be recorded along with the type of operation, the beginning of the transaction, etc.

Backup tips
Use the following checklist to make sure you don't forget crucial steps during your weekly database backups.
  • ·        Back up the master database weekly. Run a manual backup if you create, alter, or drop a database; add new SQL Server messages; add or drop linked servers; or add log devices.
  • ·        Back up the msdb database daily. It's usually pretty small, but it's important because it contains all SQL Server jobs, operators, and scheduled tasks.
  • ·        Back up the model database only if you've modified it.
  • ·        Use SQL Server Agent to schedule your backup jobs.
  • ·        If you have the resources available in your production environment, back up production databases to disk either locally or on a network server on the same switch. Then, copy the backup files/devices to tape. With many hardware failures (especially with RAID systems), the disk is usually intact. Recovery will go much faster if the backup files are on disk.
  • ·        Back up development and test databases using, at a minimum, the SIMPLE recovery model.
  • ·        In addition to regularly scheduled backups, back up user databases after nonlogged bulk operations (e.g., bulk copies), creating indexes, or changing the recovery model.
  • ·        If you're not using the SIMPLE recovery model, remember to back up your database AFTER you've truncated the transaction log.
  • ·        Document your recovery steps. At the very least, outline the steps, noting where all the important files are.


All of this information remains in the transaction log until the log is truncated—that is, until all committed transactions are purged from the log. In the SIMPLE recovery model, the log is truncated during a CHECKPOINT, when the SQL Server's memory cache is written to disk, which happens automatically but can be performed manually. That is why the SIMPLE recovery model doesn't support point-in-time recovery. With the FULL and BULK_LOGGED recovery models, the transaction log is truncated when the transaction log is backed up, unless you've explicitly specified not to truncate.

To back up a transaction log, use the BACKUP LOG command. The basic syntax is very similar to the BACKUP command:
BACKUP LOG { database } TO <backup device>

Here is an example of how to back up the transaction log to a logical device called LogBackupDevice:
BACKUP TRANSACTION Northwind TO LogBackupDevice

To prevent the truncation of the transaction log, use the NO_TRUNCATE option, like so:
BACKUP TRANSACTION Northwind TO LogBackupDevice WITH NO_TRUNCATE

Just the basics
Although I just covered the basics of database recovery in this article, you can move in the right direction by following these tips. So save yourself some panic by backing up the master weekly and the msdb daily.

Editor's Picks