Easiest way to calculate buffer count for faster SQL server backup

%3CLINGO-SUB%20id%3D%22lingo-sub-3525311%22%20slang%3D%22en-US%22%3EEasiest%20way%20to%20calculate%20buffer%20count%20for%20faster%20SQL%20server%20backup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3525311%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EBACKUP%20DATABASE%20AdventureWorks%0ATO%20DISK%20%3D%20'C%3A%5CAdventureWorks.BAK'%0AWITH%20STATS%20%3D%201%2C%20COMPRESSION%2C%20CHECKSUM%2C%20buffercount%3D20%2C%20maxtransfersize%3D2097152%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3Ebuffercount%20%3D%20NumberofBackupDevices*%203%20%2B%20NumberofBackupDevices%20%2B%20(2%20*%20NumberofVolumesInvolved)%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EI%20read%20at%20many%20places%20but%2C%20I%20really%20don%E2%80%99t%20get%20100%25%20of%20what%20is%20mentioned%20above%20to%20calculate%20buffercount%2C%20please%20help%20me%20to%20understand%20it%2C%20as%20I%20am%20not%20an%20expert%20in%20SQL%20administration.%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EAlso%2C%20is%20there%20an%20impact%20of%20the%20SQL%20version%20on%20buffercount%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

 

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\AdventureWorks.BAK'
WITH STATS = 1, COMPRESSION, CHECKSUM, buffercount=20, maxtransfersize=2097152
GO

 

 

buffercount = NumberofBackupDevices* 3 + NumberofBackupDevices + (2 * NumberofVolumesInvolved)

 

I read at many places but, I really don’t get 100% of what is mentioned above to calculate buffercount, please help me to understand it, as I am not an expert in SQL administration.

 

Also, is there an impact of the SQL version on buffercount?

0 Replies