September 25, 2009 by Mike Hillwig
Do you use database backup agents to back up your database or do you write your backups to disk and use a file system backup agent for sending them to long-term storage?
I’ve seen this spark religious debates between DBAs and network administrators.
As a network administrator, I like being able to point the backup software at a server, let it recognize that it’s a SQL instance and tell it to back it up. It’s simple.
As a DBA, the thought of using a backup agent makes me cringe. The agents work for full backups. They work incredibly well. They’re also a complete pain in the ass when you have to do a restore. As a micromanaging control freak, I like being able to tell my restore jobs EXACTLY where a file should go and how it should be named. Most agents don’t give me that granular control.
As a network administrator, the thought of writing a backup to a file first before sending it to long term storage (does anybody actually still use tape?) seems like an extra step we don’t need. It also means we have files sitting around we probably don’t need. (My inner DBA is screaming at that one.)
As a DBA, I worry about my transaction logs. I haven’t seen backup agents handle t-logs very well in the past.
As a network administrator, I like to know that my stuff is working. I trust what I control.
As a DBA, I like to know that my stuff is working. I trust what I control.
I could let my experiences in two different roles argue this out for a while, and it could get quite heated.
At my former employer, we used a backup agent. I hated it. I also had no choice in the matter. Every time I brought it up, I got shot down. In my current environment, we write everything to disk before sending anything off-site.
I’ve seen both. And I’ve seen both work. I’m a fan of writing to disk first. What do you do?