Improve Backup Performance

Brent Ozar on backup tuning:

QUESTION 1: HOW FAST CAN SQL SERVER READ DATA FROM DISK?

You can measure this by doing a full backup to disk, but use the NUL: option:

  1. BACKUP DATABASE MyDb TO DISK=‘NUL:’

This does a backup, but doesn’t actually write it anywhere, so it’s a measure of how fast the data files can be read from storage. If you run this, know that you’re taking a real backup: this backup can break your differential backups.

Vital follow-up:  Sean McCown’s talk on performance tuning for backups.  SQL Server backups have a few knobs you can turn, like buffercount, maxtransfersize, and number of files.

Related Posts

SQL Server Backups To Azure Blob Storage

Kevin Hill shows  how to configure SQL Server to back up a database to Azure blob storage: Note the “no blobs found” in the container.  After a successful backup, you will see it here. Click on ‘Container Properties’ to get the URL for this specific container…this will be used in Backup and Restore statements.  Click the […]

Read More

VSS Snapshot: Freeze & Thaw

Erik Darling points out that VSS backups aren’t instantaneous and can block queries: Ah, backups. Why are they so tough to get right? You start taking them, you find out you’re not taking enough of them, or that they’re not the right kind, or that you’re not using checksums or compression, or that you’re not […]

Read More

Categories

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