The Problem with Striped Backups

, 2009-12-23

I noticed a post recently from Bru Medishetty on Striped Backups. I commented, but thought I should explain a little more. There is a problem with striped backups in SQL Server.

They work great, they increase the speed at which you can perform a backup (possibly) and they can lower your backup window as well as reduce the load on your database server. Those are the good things.

The bad thing is they are too easy.

Yes, too easy. It sounds silly, but let me show you something. I can open Management Studio and pull up the backup dialog for one of my databases.


Now typically what I want to do is make a new backup. So I click the “Add” button in the lower right, get this dialog.


I type in my filename, and change the path if needed. I now click “OK” (it’s not grayed out when I enter a file) and I go back to the backup dialog. To make it easier to read, I’ve zoomed in at the bottom.


Or more specifically, let me scroll the box and let you see what’s there:


What I’ve actually got here is the last backup I did (a log backup file) listed along with the new one I entered. If I now click “OK” and perform the backup, what happens? Remember these file names weren’t visible from the main dialog without scrolling, and actually when I clicked “OK” from the file name, that line was actually highlighted.

What will happen is that half of my backup will get striped to 20090929.log and half will be striped to 20091216.bak. If I now want to restore the database on a new server, I need both those files.

Think it couldn’t happen? This is exactly when happened to me one time. I was in NY on business and got a call from a consulting client back in CO. They had done this, thinking they made a backup of the database, moved the file to the network and proceeded on with an upgrade. The upgrade crashed, and they couldn’t restore the .bak file. They called me and as I drove in a cab through Manhattan I had to give them the bad news that without the other file, they couldn’t restore the database. They never found that file and lost a few days worth of work.

They also abandoned SQL Server, moving to MySQL, which has simpler file based backups. They went out of business before long (it was a startup), though I don’t think that was related to MySQL.

I’ve seen new DBAs, developers, managers, any number of people have trouble here. The reason is that the GUI saves the last file you used in the dialog and presents it. Adding a new file, and seeing it highlighted, has confused many people into making a striped backup.

They’re just too easy.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads