Faster SQL Server backups are just 10 steps away with this expert advice. These days, a 100 GB database is considered the norm, and many databases are beginning to surpass the terabyte range. Contributor Greg Robidoux looks at 10 ways to achieve faster SQL Server backups.

Running SQL Server backups is one process that can take a long time. With databases continuing to grow, the time it takes to run backups also continues to grow. At one point, a 100 GB database was considered a very large database. Today it's the norm, and many databases are beginning to surpass the terabyte range. We will look at ways to achieve faster SQL Server backups.

1. Back up to disk, archive to tape

Backing up to disk is considerably faster than backing up to tape, and most experienced DBAs prefer this method. In addition to the faster I/O rates, you also have the latest backup on hand in case you need to do a restore. Once the backup to disk has completed, you should then archive to tape for long-term storage.

2. Back up during off hours

It's best to do your full backup during off hours when there is little activity on the server. But keep in mind that some batch jobs that are run during off hours can put more strain on your systems than jobs run during the day. It's important to monitor what's happening on your servers, and to schedule your full backups at the right time.

3. Use compression software

The best approach to SQL Server backups is to back up to disk and then archive to tape. The downside is that the backup files generally are about the same size as the data files. Because of this, if you have a 100 GB database you will need about 100 GB of disk space for your backup file. Unfortunately, SQL Server does not have a built-in process to compress the backup file. You can use a Zip product, but this adds extra processing time to the mix. Luckily, there are three products on the market -- from Idera, Quest Software Inc. and Red Gate Software Ltd. -- that allow you to create compressed backups on the fly. The benefits you receive from this type of product far outweigh the cost.

4. Write to multiple files

Another approach is to write your backups to multiple files so you have multiple threads for the backups. This is the approach taken by both the tape vendors and the three companies mentioned above. The ability to multitask will create your backups much faster than just using one backup file. With this approach, you won't be compressing the backup files, but it should greatly reduce the time it takes to complete the backup.

5. Write to multiple physical disk drives

Creating full backups is a very I/O-intensive process. Every data page in the database must be read and then written to another file. By having multiple physical disks, you will be able to achieve higher I/O rates and complete the process more quickly. In addition to writing to multiple files as mentioned above, it is also helpful to write to different physical disks to alleviate the I/O bottleneck