SQL Tips - Backup/Restore Optimization Tips

*****

Try to perform backup to the local hard disk first, and copy backup file(s) to the tape later.
When you perform backup, some SQL Server commands cannot be made, for example: during backup you cannot run ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options, you cannot shrink database, you cannot run CREATE INDEX statement and so on. So, to decrease the backup operation's time, you can perform backup to the local hard disk first, and then copy backup file(s) to the tape, because tape device usually much more slow than hard disks. The smaller backup operation's time is, the less impact there will be on the server when the backup occurs.

*****

Perform backup 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.

*****

Perform backup on a physical disk array, so 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 backup's data in parallel.

*****

Perform backups during periods of low database access.
Because backup is very resource effective, try to schedule it during CPU idle time and slow production periods.

*****

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

*****

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

*****

Use differential backup instead of incremental backup when the users update the same data many times.
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 differential backup, in this case, can improve the recovery process in several times.

*****

Try to separate your database to different files and filegroups to backing up only appropriate file/filegroup.
This can results in smaller backup operation's time. The smaller backup operation's time is, the less impact there will be on the server when the backup occurs.

*****

Use Windows NT Performance Monitor or Windows 2000 System Monitor to check a backup impact on the total system performance.
You can verify the following counters: 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; SQL Server Databases: Backup/Restore Throughput/sec to monitor the throughput of the entire database backup or restore operation; PhysicalDisk: % Disk Time to monitors the percentage of time that the disk is busy with read/write activity; Physical Disk Object: Avg. Disk Queue Length to determine how many system requests on average are waiting for disk access.

*****

To decrease the backup operation's time consider backing up more often.
The more often you will make backup, the smaller they will be, and the less impact there will be on the server when the backup occurs. So, to avoid locking users for a long time during everyday work, you can perform backup more often.
Note. The more often you will make backup, the less data you will lost if the database becomes corrupt.

*****

Place a tape drive on another SCSI bus as disks or a CD-ROM drive.
The tape drives perform better if they have a dedicated SCSI bus for each tape drive used. Using separate SCSI bus for a tape drive can results in maximum backup performance and prevents conflicts with other drive array access. Microsoft recommends using dedicated SCSI bus for the tape drives whose native transfer rate exceeds 50 percent of the SCSI bus speed.

*****

Use SQL Server 2000 snapshot backups for the very large databases.
The SQL Server 2000 snapshot backup and restore technologies work in conjunction with third party hardware and software vendors. The main advantages of snapshot backups and restores are that they can be done in a very short time, typically measured in seconds, not hours, and reduce the backup/restore impact on the overall server performance. The snapshot backups accomplished by splitting a mirrored set of disks or creating a copy of a disk block when it is written and required the special hardware and software.