Multiple Backup Files

I’ve been writing a little PowerShell lately that will back up databases, move files, and restore them. I’m often testing things, and having scripts to quickly and easily move files is very handy. After one of my posts recently, a reader asked if I’d considered multiple files and how to handle them in scripts. I confessed I hadn’t, mostly because I haven’t had to deal with them.

In my career, I’ve tended to work with small to medium sized databases. I’ve had young children for a large portion of my SQL Server career and had no desire to babysit multi-hour (or multi-day) restores when things break. I know some people have been through those situations, and good for them. I just know I’ve had enough issues with the low-GB sized database, and haven’t been interested in supporting TB sized systems.

However that likely wouldn’t be the case in the future. More and more companies are collecting and storing data that reaches into the TB, even in small companies. The rapid advances in sensors, development tools, and cheap storage means that many people are dealing with hundreds of GB in at least one of their databases. That means for a reasonable RTO, making quick backups, and maintaining good performance, multiple backup files are becoming a necessity.

Is that really the case? Data volumes are exploding, but you many of you using this feature? I wanted to see how many people have implemented, or at least thought of striped backups. The poll this week is:

Do you have any databases that benefit from backup to multiple files?

I’ve consulted with clients that accidentally produced striped backups and then lost one of the files. That’s never a good situation, and it’s bad news to have to give as a consultant. However, I’m sure many of you have consciously implemented striped backups because they can perform better than single file backups and make for quicker restores. Others of you may suspect (or have tested) striped backups will help your systems but haven’t gotten around to setting things up.

Let us know if this is a common feature you use, or is it still something esoteric that you have no need for. And if you have put striped backups in place, have you tested a striped restore? I certainly hope so. Let us know either way.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.7MB) podcast or subscribe to the feed at iTunes and LibSyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , , . Bookmark the permalink.

2 Responses to Multiple Backup Files

  1. Brian K says:

    I just saw a striped backup for the first time in the wild the other day, during my first week in a new job. Wasn’t even the biggest database I’ve ever seen (it’s in the hundreds of GB), but now my interest is piqued.

  2. sqlmoto says:

    I use striped backup files now with our ~500gb db’s because we are using P2P replication, and restarting the replication via backup when it breaks requires the info on the header (on the first) file. This allows me to do the restore faster on the slave and also have a smaller file to read the header info for the replication restart. I’ve also used multi-file backups with my previous job with multi-TB reporting db’s for speed reasons. It helps quite a bit if I/O is your bottleneck in the restore, which we were, even though the files were compressed. Redgate’s file naming capabilities help to keep things straight to reduce the chance of accidentally deleting the wrong thing.

Comments are closed.