Backup Optimization

• adb2303

  SSCertifiable

  Points: 7588

  More actions

  August 31, 2011 at 4:24 am

  #247356

  Hi,

  I've just finished testing our backups with varying BUFFERCOUNT and MAXTRANSFERSIZE settings, and for our databases (with Hyperbac), have found that a BUFFERCOUNT of 50 and MAXTRANSFERSIZE of 4MB shaves upto 25% off backup times.

  Is there a way to configure these as settings as a global default, so that all subsequent backups use it? I don't want to have to change all our existing maintenance plans to use a script, rather than the built in backup task.

  We're running 64bit SQL Server 2005 Enterprise Edition, SP3. 128GB RAM

  Thanks,

  Andrew

• Grant Fritchey

  SSC Guru

  Points: 399016

  More actions

  August 31, 2011 at 7:24 am

  #1375459

  To my knowledge, this needs to be part of the backup command. I don't think there's a way to set a default value for all backups. I'm doing some checking though to see if I can determine otherwise.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• adb2303

  SSCertifiable

  Points: 7588

  More actions

  August 31, 2011 at 7:30 am

  #1375462

  OK. Thanks for your help