Voice of the DBA

Tag: Backup/Recovery

  • Transactionally Consistent

    What does this mean? I had someone ask me in a session recently, and I think I have a good explanation, but I thought this was worth a blog.

    Transactionally Consistent means that there is a snapshot of the database at a point in time that ensures committed transactions are actually reflected in the database and uncommitted transactions are not. I haven’t found a better concise definition, but Paul Randal has a great longer explanation.

    This is reflected in the ACID RDBMS model as the C for consistent.

    In a SQL Server backup process, this is accounted for by including some amount of the transaction log in the full database backup. The amount included depends on your workload and the amount of data you back up, and if you read Paul’s explanation, you’ll understand more.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

  • Risky Backups

    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.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    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.

    Overall RSS Feed:  or now on iTunes! 

    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.

  • It Still Happens – Full Transaction Log

    Probably the most often asked question on SSC is about full transaction logs, and often full disks. It happened again recently. I don’t have an exact count of threads, but rough searches show this to have 70,000+ results, when most other searches I run show many, many fewer results in the forums.

    I talked about this recently in my backup talk on the SQL in the City tour stops. Back up your logs.

  • The Need for Tape

    This editorial was originally published on Mar 26, 2009. It is being re-run as Steve is away at DevConnections.

    One of our SQLServerCentral authors sent me this post, which I found to be pretty amazing. Apparently hackers broke into a backup server at WebHostingTalk, which is a community for hosting providers to discuss issues. They deleted backups first, and then moved on to deleting tables from the database.  It resulted in some downtime, and then a restore of an old copy of their database prior to trying to restore more recent copies. More information from the admin is here.

    Now that’s just malicious and nasty.

    Hopefully this wasn’t an inside job, though I could see that as a possibility. For a service like this, that just provides a place for a community, let’s people interact and talk, this is just vandalism. It serves no purpose. It’s likely no one even knows it was “M@M@sB0Y” or some other hacker, so there’s no fame, and it disrupts people who just want to converse with colleagues.

    I’d hate to think about this happening here. We have lots, and lots of posts from people all over the world, and while it wouldn’t kill us, it would really annoy many people that have volunteered their valuable time to help others by losing their work. We definitely need to make sure we don’t have an issue here.

    I see two takeaways from this incident for most DBAs and administrators. First be sure that your backup servers are just as secure as your production ones. There is production data in the forum of backup files here, and you should be providing as much security for them, and perhaps more, as on other servers. Don’t treat these servers lightly.

    Second I think this shows that there’s a need for tape backups, or some type of non-linked backup. A tape grabs the files from your server, completely separately from the SQL Server (or other application). It is a pull link, and it’s not obvious from the source server where these files have gone. That’s good in that it prevents some type of attack on the main server from propagating on. Most people use a push from their server to a remote device as part of the backup process. That’s OK, but it provides an easy link for someone to attack the backup server from the main one.

    And one more benefit of tape? Usually they’re rotated out, so even if a hacker or insider knows how to get to the backup system, without physical access they can’t touch your tape.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    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.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.