Who Owns SQL Backups?

Reliable Backups
Who Owns SQL Backups?
I was asked by Idera to take a look at the things that could cost a DBA their job. This has lead to a new whitepaper I wrote about the 5 mistakes that DBAs make that can cost them their jobs. Watch Idera’s Resource Central or follow @Idera_Software on Twitter to see when the whitepaper is released. In this post, I’m going to take a peek at one of the mistakes.

This is a topic I hear come up time and time again. Who owns backups? I’ve heard arguments that the systems team owns backups of everything, and therefore should own SQL Server backups as well. I’ve heard arguments that since SQL is on the SAN, the SAN admin should own SQL backups. I always argue that the DBA, and the DBA alone, owns SQL Server backups. It is a responsibility that I, as a DBA, would never relinquish to someone else. I will lay out my reasons below.

Who Owns Restores

I have found that one of the most compelling arguments to a systems engineer or SAN engineer is that if they want to own backups, they have to own restores as well. Are they going to restore SQL databases on the servers if one is needed? What if it needs to be restored to a specific point in time? Or we need to restore only part of a backup like a page or a filegroup?

There are non-SQL backup tools that make backups easy to do if you don’t have to worry about complex restore scenarios. If I need to be able to do complex restores, I need to know that the backups to support those restores exist and that whatever backup medium that is used is able to support the kind of restore I need to do. I can’t know that if I’m not in charge of the backups.

When I worked at Microsoft, we were required to start using the in-house Data Protection Service (DPS) that the datacenter IT team had set up. It was built on top of Data Protection Manager. It took some time, but I finally got access to the backup service’s reporting tool for seeing the status of our backups they were performing. We had backups that had been failing for 6 weeks and nobody on the DPS team was even looking into the failures.

What if I had lost one of those databases and needed to restore it? DPS would not have been able to provide me with a restore. Fortunately, we had chosen to continue doing regular SQL backups until DPS could prove that they were backing up the databases. In addition to backups failing, DPS could only support restoring a full database to a 30 minute snapshot. Not good enough for what we needed to support.

Who Will Be Held Responsible if a Database Cannot be Restored?

One of my biggest reasons for insisting that the DBAs own backups is because if a disaster happens, and the database cannot be recovered from backup, the DBA will be the one held accountable for it, no matter who was assigned responsibility to do the backups. I’ve seen suggestions in forums telling people to get a written statement from the SAN admin or systems team that they will be responsible for it, but when it really comes down to it, you are responsible for whether or not you can do your job. Being blissfully ignorant is not an excuse. If you cannot restore from backup, you will be held accountable no matter who agrees ahead of time that you won’t be.

Reliability of Backups

How will you know that the backups are reliable if you don’t actively work with those backups? If someone else is backing up your databases, are they getting verified? Is someone testing the backups by doing test restores? Are they doing disaster recovery drills practicing restoring the backups? A backup is not very useful if it can’t be restored. You need to ensure that the backup is restorable and that the person who will be restoring it can restore it. This means testing backups and testing the restore process. ROUTINELY. If you’re not doing this yourself, you need to start.

Does their backup medium support backing up with the CHECKSUM option? This option is very important for making sure that your backups do not contain corrupted data. It is needed to ensure that you are creating good, non-corrupted databases. Everyone should be doing this for all backups and restores.

What Do You Think?

I think my reasons are pretty convincing for DBAs to be responsible for backups. What do you think? Do you have other arguments for or against DBAs owning backups? Let me know your thoughts.

*Reposted with permission from SQLSoldier.com.

(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *