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:
- 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.
10. To decrease the time taken by the backup operation, consider backing up
more often.
The more often you make backups, 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 backups more often.
Note: Another benefit to this is that the more often you make backups,
the less data you will lose if the database becomes corrupt.
11. Place a tape drive on a different SCSI bus from the disks or CD-ROM drives.
The tape drives perform better if they have a dedicated SCSI bus for
each tape drive used. Using a separate SCSI bus for a tape drive can
result in maximum backup performance and can prevent conflicts with
other drive array access. Microsoft recommends using a dedicated SCSI
bus for the tape drives whose native transfer rate exceeds 50 percent
of the SCSI bus speed.
12. Use SQL Server 2000 snapshot backups for 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.
Snapshot backups are accomplished by splitting a mirrored set of disks
or creating a copy of a disk block when it is written and require
special hardware and software.
»
See All Articles by Columnist Alexander Chigrik