Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 9, 2002

SQL Server Backup/Restore Optimization Tips

By Alexander Chigrik

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.

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM