Although backing up or restoring a database from a DBMS like SQL Server 2000 is always a major operation, according to Microsoft there are a number of things you can do to improve the performance considerably.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
Maximizing performance starts with architecture. In general spreading the SQL Server database over more disks gives better performance, both in ordinary operations and in backup. Ideally, Microsoft recommends striping the database files evenly across multiple disks to improve I/O. Similarly, multiple backup devices, whether tape drives or disks, improve parallelism and hence performance.
Next, look at your backup software. A number of third party vendors have backup products that use Microsoft's Virtual Device Interface (VDI) for Backup to transfer data for backup using shared memory architecture to increase the speed of the operation.
If you are backing up to a local RAID array, use RAID level 1 or RAID level 10 rather than RAID 5. RAID 1 or 10 is about half as fast as RAID 0 (which provides no data protection), but about twice as fast as RAID 5.
Another trick is copying the database to disk and then copying to tape from the copy. This cuts the time the primary database is slowed down by backing up.
Also consider the use of log shipping. Log shipping doesn't speed up backups and technically it isn't even a restoration method, but it can have you up an running in the event of a server crash very quickly. Log shipping lets the you maintain a standby server which is synchronized almost real time to the production server. When transaction logs are backed up on the primary server, a copy of the log is automatically sent to the standby server and restored there. In the event of a server failure, the standby server is brought on line manually to handle application requests. To reduce the number of lost transactions, and if there is still connectivity to the primary server, try to backup, copy and apply the orphaned log (NO_TRUNCATE)
Microsoft discusses performance tuning of SQL Server 2000 in Chapter 4 of the SQL Server 2000 Operation Guide, available on Microsoft's website http://www.microsoft.com
For more information:Tip: Snapshots in SQL Server 2000
Tip: The problems backing up big databases
Tip: Tune for RAID performance
About the author: Rick Cook has been writing about mass storage since the days when the term meant an 80K floppy disk. The computers he learned on used ferrite cores and magnetic drums. For the last twenty years he has been a freelance writer specializing in storage and other computer issues.