SQL Server Backup/Restore Optimization Tips

December 9, 2002

Here are twelve helpful tips to ensure you are performing SQL Server backup/restore operations in the most efficient manner possible.

1. Try to perform backups to the local hard disk first, while copying copy backup file(s) to tape later.

When you perform a backup, some SQL Server commands cannot be made; for example, during backup you cannot run an ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink a database, you cannot run a CREATE INDEX statement, and so on.

So, to decrease the backup operation's time, you can perform the backup to the local hard disk first, and then copy backup file(s) to tape later, as a tape device is usually much slower than a hard disk. The faster the backup operation, the less impact there will be on the server when the backup occurs.

2. Perform backups on multiple backup devices.

Using multiple backup devices forces SQL Server to create a separate backup thread for each backup device, so the backups will be written to all backup devices in parallel.

3. Perform backups on a physical disk array, as the more disks in array, the more quickly the backup will be made.

This can improve performance because a separate thread will be created for each backup device on each disk in order to write the data for the backup in parallel.

4. Perform backups during periods of low database access.

Because backups are very resource effective, try to schedule them during CPU idle time and slow production periods.

5. Use full backups to minimize the time to restore databases.

Full backups take the longest to perform in comparison with differential and incremental backups, but they are the fastest to restore.

6. Use incremental backup to minimize the time to backup databases. Incremental backups perform the fastest in comparison with full and differential backups, but they take the longest to restore.

7. Use differential backups instead of incremental backups when the users regularly update the same data.

Because a differential backup captures only those data pages that have changed after the last database backup, you can eliminate much of the time the server spends rolling transactions forward when recovering transaction logs from the incremental backups. Using a differential backup, in this case, can improve the recovery process by several times.

8. Try to separate your database into different files and filegroups so that you can back up only the appropriate file/filegroup.

This can result in quicker backup operations. The faster the backup operation takes, the less impact there will be on the server when the backup occurs.

9. Use the Windows NT Performance Monitor or the Windows 2000 System Monitor to check a backup's impact on total system performance.

You can verify the following counters:

1. SQL Server Backup Device: Device Throughput Bytes/sec to determine the throughput of specific backup devices, rather than the entire database backup or restore operation;
2. SQL Server Databases: Backup/Restore Throughput/sec to monitor the throughput of the entire database backup or restore operation;
3. PhysicalDisk: % Disk Time to monitors the percentage of time that the disk is busy with read/write activity;
4. Physical Disk Object: Avg. Disk Queue Length to determine how many system requests on average are waiting for disk access.