SQL Server Backup – Inadvertent Striping

When I started working with SQL Server I got bit in the rear one day while I was testing backups. I was in the process of making a quick backup before I deployed some changes. I think this was in the Enterprise Manager days of SQL 2000, but it could have been v6.5. In any case, I had a dialog similar to this one that I’ve shown in Management Studio. For simplicity I’ve recreated this with AdventureWorks:


No biggie, right? I click add, and enter my new file for the backup:


I accept this, and highlight me new file and click “OK” to do the backup. That will work, won’t it?


Actually it won’t. or rather, it will but not in the way I expect.

What this will do is create a striped backup (search in this article for “striped). I will have my data in a backup, but I will need both of these files in order to do a restore as each will only have half my data.

It’s a rookie mistake, one that’s not possible with a script, which is why you should backup with scripts, not the GUI.

However if you notice this, delete all the files, add yours back, and make a full backup right away. Chances are that you might have broken your recovery chain. And make sure you let everyone know this is not what you want to encounter.

About way0utwest

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