This editorial was originally published on Jan 13, 2009. It is being re-run as Steve is away at the SQL Intersection conference.
The other day I was working in the forums at SQLServerCentral and saw a question about someone doing backups and having issues. While myself and others were trying to help, I recommended that this person not run multiple backups to one file. It’s something I’ve done for years and I’ve recommended, but the person followed up with a question about why I think it’s important.
My first answer was that it’s an unnecessary risk. If something happens to the file and you’ve stored multiple backups in this file, then you lose all the backups. I’ve seen this happen with older versions of SQL Server and after that time I made it a point to never stick multiple backups in one file. To me it’s creating a single point of failure for no reason.
But as I wrote this, I started to question myself a little. I know that some people like having the same filename every day as it makes restores simpler, especially if they’ve scripted things. I know that file systems and hardware is often redundant and files are generally safer in the short term than they were in the past, so is my advice outdated?
I still don’t think so because in my mind there’s a great risk of issues here and that outweighs the convenience. Not to mention that some simple scripting can make restoring to something like QA easy enough for anyone to run. However I’m curious what the rest of you think about storing multiple backups in one file.
The Voice of the DBA Podcasts
The podcast feeds are now available atsqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure🙂. Comments are definitely appreciated and wanted, and you can get feeds from there.
Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.