Optimising Backups

stevew

I'm new to SQL Backup and need some help to understand the best practice on optimising backups for large databases. I'm using SQL Backup V5.1 on 210GB database in a Production environment and currently it's been running for over four hours. The backup is set compression level 1 and is writing to four data files. It's on a W2003 Standard SP1 platform on a HP Proliant 560 with 4CPU's and 4GB of RAM. The system doesn't seem stressed in any way but I'd like to have some idea of when the backup will finish and if what the optimised set up is.

petey

You can run the sqbstatus extended stored procedure to determine how much data has been backed up.

IMO, optimising backups depends a lot on the sort of disk subsystem you have. The same principles apply to native SQL Server backups: if you can, create the backups on disk(s) that do not contain the database data or log files. Increase the number of threads until either the CPU or the disk write queue length hits the recommended limits.

See the section on optimising backups in the help file for hints. In your case, testing on a 210 GB database in a production environment is probably not a good idea. Would be good if you have a smaller test database, placed on the same disks as the production database, to perform the tests on.

stevew

Thanks for the info, I've gone through the optimisation process and found the following optimised settings on a 4GB db is
Database size : 4.110 GB
Compressed data size: 830.275 MB
Compression rate : 80.27%
Data rate : 5.912 MB/sec
Duration : 713.141 seconds
with three threads
Unfortunately the database files and backups are on the same disks however this is how it was passed to me. This means the large db should take around 9 hours to complete, the problem that I've encountered previously when it has been backing up is the system performance has been degraded due to disk i/o.
I'm in a catch 22 situation, the reason for purchasing SQL Backup was for the compression as we didn't have sufficient space to backup the database. It looks like I'll need to review the server set up especially the disk subsystem to get the level of performance we need for this environment.