I have been digging into backups for so long now.
I’ve been involved in large database enterprises for over twenty years. Often I was the only DBA (or one of only two or three DBAs) monitoring thousands of SQL boxes, with tens of thousands of databases on them. Those environments were high in pressure, and low in “anybody else is around to do anything”, so I learned quite a lot about most aspects of SQL Server administration. In the mid 1990s I started learning how to tune backups. I did a lot of experimenting and pinging the SQL Server backup team. From those experiences, I learned to tune backups.
I have seen only a handful articles and white papers on backup and restore performance tuning in all my time in SQL Server. The vast majority of these come down to two things: reducing the bits on disk (via compressionfulls), and multiple files. These are fine as far as they go, but strictly speaking, that’s not backup tuning, any more than “use smaller tables and fast disks” is the whole of database performance tuning.
I’ll be putting together more material on the topic, but for now, here is the abstract, recording, and demo code of my backup tuning session.
The Backup Tune-up
Abstract: Have you ever gotten tired of your database taking hours to back up? Are you sick of your users breathing down your neck because the database restore is taking too long? Now you won’t have to worry about that anymore. In this session I show you some little known tricks, methods, and trace flags you can use to tune your backups just like you would a query. You’ll learn about the backup “execution plan” and how to access it. Understanding how to tune the individual portions of your backup process will allow you to knock 80% and even more off of your backup and restore time. I’m not holding anything back in this session. This is a method I’ve used for 15 years to tune my backups and I’ve had great success.