I was in Phoenix last week to visit a customer, and I saw an email come through regarding a customer who contacted support because they discovered they were missing data from a table. The customer contacted support on Friday morning and our team was engaged almost immediately. The data loss had been discovered on Thursday afternoon. When we asked about a database backup to restore, so we could recover the data from it, we were told that they did have a database backup because they backup nightly. Hooray! They backup nightly! However, the backup was from Tuesday night, after the data was found to be missing. Apparently, they overwrite the backup every night.
Ouch. I immediately asked if they had an off-site copy (they did not) and then I asked why they only have one day’s backup available. The answer? They have issues with disk space.
One of my next thoughts was, “How many DBAs ask for backup space when they’re asking for space for a database?” So to any DBAs who might be reading this: when you are asked how much disk space you need for a database, do you also include backup storage? Do you include test, development or QA environments?
Then I started thinking about the documentation that our team provides to customers. Do we ever mention disk space for backups? We do not. We have no specific recommendation regarding how much space is needed for backups, but…we do talk about backing up the database to separate storage. That’s probably not enough. Conveniently, we’re in the process of updating those guides, so I’m going to add a section covering disk space for backups. And space for testing restores. And space for test, development and QA environments. Am I greedy? Yes, I am. But can I afford not to be?
And in case you’re wondering about the customer and their lost data…a team member was able to recover it using Lumigent’s Log Explorer tool. Some of you may have heard of it. It has saved data for a few customers over the years. Sadly, it doesn’t work past SQL Server 2005 and you’d have to do some serious searching to find the files online. It has a few quirks when you’re working with it, but all in all, it’s been a very useful application for us. However, a third party tool is no replacement for a sound recovery strategy.
So when you have a minute today, go check the storage where backups are stored. How much space do you have? How many backups are you keeping online, and how often are backups going off site? And finally, if you had to recover, right now, from a disaster that occurred yesterday afternoon, could you get the backup that you needed, and how quickly?
