SQL 2000 Backup Problems

[SQL2000]

I have a big database of more than 20 GB running on SQL
2000 with SP1 on Win 2K SP2 Server. The Database is in
Raid 5. Every day I do a full backup and the backup
operation takes about a total of 40 minutes. Of this 40
minutes the first 30 minutes I am able to connect to the
server and there is no problem and in the last 10 minutes
there seems to be a lot of disk activity when it tries to
write the data in the disk and at this time none of the
application is able to connet and all my applications
fails or in the other words the server freezes. After that
everything works fine. Is there a way to avoid this. I
guess SQL Server should handle a large databases and 20 GB
is not that big and I do not know why there is a
connection failure when it is writing data into the disk.
The server has dual III 1 GHz processor with 2 GB ram.

Thanks,

Ray.

 

[Hal10K]

You could do SQL style raid, which is using multiple backup devices for each backup, and spread the i/o load across multiple spindles.

This is from the SQL 7.0 BOL, but it also applies to SQL2K.

BOL Query terms: [backup NEAR "File Group"], and one of the topics returned is "Scalability", which says, in part

Very Large Database (VLDB) Improvements
SQL Server has high speed optimizations that support VLDB environments. Earlier versions of SQL Server could support databases up to 200 MB or 300 MB in size. SQL Server 7.0 can support terabyte databases effectively.

The Transact-SQL BACKUP and RESTORE statements have been optimized to read through a database serially and write in parallel to multiple backup devices. Sites can also reduce the amount of data to be backed up by performing incremental backups that back up only data changed after the last backup, or by backing up individual files or file groups.

Multiple bulk copy operations can be performed concurrently against a single table to speed data entry.

Operations that create multiple indexes on a table can now create them concurrently.

SQL Server databases now map directly to Windows files, simplifying the creation and administration of databases. The database page size has been increased to 8 KB and extents to 64 KB, which results in improved I/O.

 

[Terryute]

Have you tried incremental log backups? Backing up the log file is much faster.

How it works:

All current data being read to and from the database is actually going in your log file. At a checkpoint the data is transferred to the actual database file.

If you fully backup your database on Sunday and then keep the incremental log backups until next Sunday, you will have full recovery for any period up to the last log backup.

After the backup the following sunday you can remove the incremental log backups and start over.

Look in BOL for a detailed explanation.
Good Luck.