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.