SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Filed under: Blog Tagged: Backup/Recovery, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by clubbavich on 19 July 2011

When doing Ad-Hoc backups outside of my regular backup schedule, I always use the Copy Only Backup option, which prevents the backup from affecting the recovery chain.

Posted by Steve Jones on 25 July 2011

Interesting, but this has nothing to do with copy only or the recovery chain. If you do this, then you need two (or more)files to restore the backup, whether it's copy only or not.

Leave a Comment

Please register or log in to leave a comment.