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

Are Backup Devices Obsolete?

I thought they were, but I've had a bit of a debate in the forums with someone. There was a question on backup schemes and one person mentioned they use backup devices and send the full and differentials to one file behind the device and logs to another.  We went back and forth a bit and I think that it's a sound strategy, but it's not one I've employed.

I smell an editorial, or possibly a Friday poll on this one, but I've felt that backup devices were an older technology, left over from Sybase, and not needed in the SQL Server world. I have had a few places where I've seen people dumping dozens of backups into one file and then having that file get corrupted. Granted that was in the 90's, in teh v6.5 and v7.0 days, but I still have been nervous about putting too many backups into one file. Once the scripting mechanisms got better in SQL Server, I was only using file based backups (BACKUP TO DISK) with a new filename for each backup, with the time and date included in the filename.

Personally I think that it's safer to use a separate file for each backup, especially if you have any concerns over disk space or you're getting low. The last thing I'd want is a partially written file run out of space. Who knows if you'd still have good backups.

But I'm not sure I think backup devices are wrong. Listening to the debate in the forums, I think the DBA (a banker) is doing a good job and being careful with his backups, so I wouldn't say he's in any way negligent. I still think my way is better, but perhaps I should solicit other opinions. 

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


No comments.

Leave a Comment

Please register or log in to leave a comment.