Every day, your servers do hundreds or thousands of operations that are all resource-intensive. You want these operations to finish as quickly as possible, because they’re absolutely mission critical, and you’ll lose your job if they’re not done successfully. Whole companies have been lost when these operations fail. Yet do you really know how to make these operations happen faster?
These operations are backups and restores.
And no, most of us don’t know why they take so long.
In this five-minute video, I explain the only two surefire ways to improve your backup and restore performance: instant file initialization and cleaning your MSDB history.
To learn more, here’s related links for each of those techniques:
- Instant File Initialization: What, Why, and How by Kimberly Tripp
- Instant File Initialization Misconceptions by Paul Randal
- Brent’s Backup Bottleneck: MSDB Histories
For fun (yes I know what I do for fun can be a bit odd at times) I logged into an older server that had SQL Server 2000 on it. At one time it had been a production instance, long since migrated over. However, the back ups were still running on there because although the database isn’t in production the server remains on-line as sort of a file archive server. I found MSDB had back up sets since early 2004. I went and deleted everything through 2008 using “sp_delete_backuphistory”. It took about 25 minutes. I went back and saw this gave a significant performance boost.
Great, glad I could help!
Hey Brent – your second link is actually by me, not Kimberly
Doh! Sorry about that, fixing it now.
[...] How to Make SQL Server Backups and Restores Faster – No nonsense, effective tips that you can put into action right now, delivered in person from Brent Ozar via the wonder that is YouTube. [...]
Hi Brent,
Nice post, its good to get that information out as its often over looked. But i have to say there are other ways that i`m sure your familiar with to improve the throughput but none of them are quick wins like the 2 you mention.
A