Tuning SQL Server Backups

Derik Hammer has a post on tuning SQL Server backups:

Finally, do not forget about your memory. To backup or restore a database you have to load data pages into memory. We will talk more about memory below and how the internal buffer pool comes into play and can cause operating system paging or out of memory conditions.

Derik shows the various knobs and switches available, and I want to emphasize one thing:  optimizing backup statements involves testing different scenarios.  You can make good guesses as to the appropriate MAX_TRANSFER_SIZE or BUFFERCOUNT, but even then, test different combinations and find what works best for each database.

Related Posts

Restoration With Replacement

Joey D’Antoni tests whether RESTORE WITH REPLACE is functionally different from dropping a database and performing a restoration: I recently read something that said using the RESTORE WITH REPLACE command could be faster than dropping a database and then performing a RESTORE, because the shell of the file could be used and therefore skip file […]

Read More

Backup Compression And Encryption

Arun Sirpal shows the combined effects of backup encryption and backup compression in SQL Server 2017: Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t?  You will get the dreaded thumbprint error. […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031