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

Backups of backups or How long is my backup really available?

I’ve said before that backups are at once one of the easiest things DBAs do, one of the most important, and one of the most complicated. Take a full backup, restore it. Pretty simple right? And yet it’s vital when accident or corruption require recovering data. And as simple as it can be on the surface, the more you dig, the more there is to know, and the more complicated it can become. Well, one of those complications is the backup of the backup files. I mean, assuming you are using native backups, that full backup is sitting on a drive somewhere, and hopefully, that drive gets backed up right?

Why? Well, for performance purposes you probably back up your databases locally. To a drive attached to the server. Now you may not, heck you could be backing up to Azure, but for the sake of this argument let’s say you are. Part of a careful disaster recovery plan is making sure you have access to those backups. I’ve heard stories of entire data centers going underwater (literally). You need to at least have a copy of your backups in a separate system, separate location from production.

You know what, let’s follow a quick example.

  1. Sunday at 2am you take a full database backup to H:\backups\DBName_20180114.bak.
  2. Monday at 2am there is a system backup of all of the drives (well probably excepting the database files themselves). It might go to tape, it might go somewhere else. Either way, it goes off-site.
  3. Two weeks later DBName_20180114.bak gets deleted from the H drive. It might be because you are running low on space, or maybe have a schedule that deletes files after two weeks. Doesn’t really matter.
  4. Two days later you find out that a user deleted some vital information on the 17th.

Is that backup file still available? How do you get it back?

One of our most important tasks is the protection, and yes, recovery, of the data we are the stewards of. So you absolutely have to know where your backups are, how long they will be available and how to get to them. An important part of that is understanding how the files (specifically the backup files) of your servers are backed up.

  • Full backups – Much like databases, file systems have a full backup. This is every file in the area/drive being backed up.
  • Incremental backups – This is a bit more complicated. Basically, an incremental backup backs up every file that has changed since the last backup (full or incremental). Generally, backup files don’t change (although you can add additional backups to an existing file, thus changing it). This means that in general any new database backups will be backed up with the incremental.

Assuming there is a full backup taken on Sundays, and an incremental taken daily and you have a similar setup for your database backups (full backup Saturday, daily differentials, logs every 10 minutes). If you delete your database backups after two weeks, and your file backups after 30 days, then one day you could easily have a full backup as far back as 40+ days ago and the next only ~35.

Now hopefully you’ll never need a backup that old, but it does happen. And sometimes you need to know the reverse. A customer has said they want certain data deleted off of your systems. You need to know how long your backups of that data are going to exist.

Finally, when practicing your DR, make sure you also practice the process of getting those file system backups back. Practice at getting back those files (and knowledge of how long it will actually take) could radically affect your RTO and could save your job.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...