What are the challenges you face when working across database platforms? Take the survey
Options

Optimising Backups

stevewstevew Posts: 3
edited July 9, 2007 7:04AM in SQL Backup Previous Versions
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.
SteveW

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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.
    SteveW
Sign In or Register to comment.