SQL Server backups are a “funny” topic to talk about. If you are talking with experienced DBAs, the topic of backups rarely surfaces, as this is a fundamental skill most DBAs learn early in their career. On the other hand, there are thousands of part-time, accidental DBAs who are tasked with making backups, and they need a lot of help in learning these basics.

Here is what I mean. I recently saw a forum post on SQLServerCentral.com asking basic questions about SQL Server backups. When I first looked at it, the answers seemed obvious to me, as I have been making SQL Server backups for years. Then I began to think about when I was a new DBA, and to be honest with you, I had the same kind of questions this forum poster did. So I answered his questions, and hopefully he will be able to use my advice as he develops his backup plan.

There are two points I want to make. First, if you are a new DBA, and you have questions, please feel free to ask them, no matter how basic they are. There is no such thing as a “dumb” question. Second, if you are an experienced DBA, take some of your time to help new DBAs out by answering their questions, no matter how basic they may be. Some experienced DBAs may be saying to themselves, “Why can’t novice DBAs look up these basic questions up in Books Online or on the Internet?” Of course they can, but sometimes this is not as easy as it sounds. Besides, it only takes a couple of minutes to help answer these questions, so why not go ahead and answer them, and help our fellow DBAs out. This is true whether it is at work, a user group meeting, a conference, or a public forum.

Below are his questions, along with my answers.

Below are some quick answers to your questions, but keep in mind, that these suggestions are generic, and may or may not be the best for your particular situation.

Q: How often do you take Full, Differential, and Transaction Log backups?
A: I make full backups daily, and transaction log backups hourly (or sometimes more often). I avoid differential backups, but sometimes they are necessary if your databases are large and your maintenance window is small. Another way around the problem of a small backup window is to use a backup compression program that saves both time and backup space.

Q: Do you backup to disk, tape, or other logical device?
A: I generally backup to a local disk, then move the backup off to other storage, which might be tape, another server, or SAN. Some people prefer to backup directly over the network directly to another disk (assuming your network can support the bandwidth).

Q: Are backups made to multiple locations via MIRROR TO?
A: They can be. Sometimes I do this, and sometimes not. It depends on my high availability goals and my disaster recovery plan, along with any resource limitations I might have.

Q: How long do you keep your backups?
A: This is a tough one, as your industry may have specific legal requirements, or your company may have specific data retention requirements. If your company doesn’t have such requirements, I always keep backups at least a month, at the very minimum.

Q: Have you ever needed a backup for disaster recovery?
A: Yes. All DBAs, at some time during their career, will need to restore a backup.

Q: If so, was there anything missing that was overlooked with your backup plan?
A: To prevent any problems with a backup plan, you need to test your backups regularly to see if they are working correctly. You also need a formal disaster recovery plan, and you need to test it.