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