Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

Question

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

psred

SSCrazy

Points: 2049
More actions
December 19, 2016 at 9:55 am

#319859

I want to know how to Set BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command for faster backup. I have a 700gb db trying to backup to a disk but its running very slow.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

psred (12/19/2016)
I want to know how to Set BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command for faster backup. I have a 700gb db trying to backup to a disk but its running very slow.

There may be a host of other factors impacting the process. While I don't have an answer on those parameters, it may be wise to see whether you have any significant database file fragmentation, as that could have an adverse impact pretty much regardless of any settings. Take a look-see on the fragmentation, and at least then you'll know if any settings changes are likely to do any good if your disk drives have to constantly seek new tracks because of database file fragments being all over the place.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 2

what is too slow?

And how are you doing the backup? single file? multiple files? with compression or without? and are files on same drive as database files or on a different drive? is the destination disk local, network or SAN?

psred SSCrazy Points: 2049 More actions · Answer 3

Backup is too slow. Am doing backup to a single file with compression enabled at server level for backups on a separate backup drive than db file drive and is local on the sever.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

how does it improve if you backup onto 4 backup files, and explicitly enable compression on the backup command?

And how long does it take to backup using a single file?

and look here for some explanation of the settings you asked - but they are trial and error until you find the best values for your specific server spec. http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks