Creating A Backup Plan

I have llisted some articles concerning this topic.

http://articles.techrepublic.com.com/5100-10878_11-1043671.html

http://www.devx.com/getHelpOn/10MinuteSolution/16507/1954

http://vyaskn.tripod.com/sql_server_administration_best_practices.htm

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. If your network can support this, this option should work fine.

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.

You can find out more about my thoughts about backups in my book, "Brad's Sure Guide to SQL Server Maintenance Plans" at: http://www.red-gate.com/products/SQL_Backup/offers/backup_maintenance_plans.htm

Mostly, believe it or not, these are technical questions, they're business questions. Ask your business how much data loss they're willing to put up with? That will begin to fill in all the answers to your questions, real quick.

Dave, here are some items easy to get wrong when coming up with a backup plan:

1) Not backing up the transaction log when your DB is in FULL recovery mode. Your t-log, if set to auto-grow, will grow without limit.

2) When using the Maintenance Plan to clear out old backup files, you (a) have to create 2 separate tasks, one to remove *.BAK and one for *.TRN files and (b) you should not put a period in the box for the file extension to remove (IOW, enter "BAK", not ".BAK").

3) Not testing your backups.;-) Seriously, even if you do this just 4 or 5 times, you'll be much better off. When I was testing this, I built a dummy database with an Agent that inserted dummy data every 5 minutes, including a timestamp column. Then I deleted, restored, inserted, did point-in-time restores -- whatever. I even proved to myself that this website (http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx is correct: you can restore on Friday to a point-in-time on Thursday using Monday's FULL backup if you have all the t-log backups from Monday to Thursday. Performing a FULL backup does not break the backup chain.

=============================================================

Our configuration is pretty typical, I think: daily FULL backup, a DIFFERENTIAL mid-day, T-LOG backups throughout the day every 15 minutes. (Actually, I changed the Agent job to only do T-LOG backups M-F, 8-5: we have negligible CUD activity after hours (mostly just SELECT), and this cuts down on the number of files generated.) SQL backs up to disk, then a daily network-wide backup program backs up the server to tape.

My cleanup tasks deliberately leave 4 days of SQL backups on the server: that way, if the network tape backup fails over a weekend, I've still got the files. Also, if I need to do a partial restore of some recently-deleted development stuff, I can grab it without waiting for the tape library to mount the correct tape.

I also backup logins, which you'll find written up here:

http://support.microsoft.com/kb/918992/[/url]

We don't currently mirror, but I'm looking into this. We'll be using an off-site mirror that doesn't rely on SQL Server to mirror our files, b/c we have other mission-critical files that need mirroring, too.

Good luck!

Rich

Thank you all for your input. It has been very helpful. I made my initial posting after I created some documentation for our backup/DR plan. As I was reviewing the plan I created and all the steps involved, I started to question my choices. I did make some changes based on your input, and here is what I've got in place:

• Full backups weekly on Sunday

• Differential backups weekdays after hours

• Transaction log backups every half hour during "business hours" (all db's have FULL recovery model)

After the full backups on Sunday, I delete all the previous full backups from disk. I also delete all the differential and transaction log backups. (I'm still debating if I should keep a few weeks worth of backup files...)

All backup files are created on a SAN that also contains the mdf, ldf, and ndf files. Copies of the backup files are mirrored to a second SAN that is not directly used by the Sql Server instance. Our network admin creates tape backups, which are transferred offsite. Those tapes include all of the full/differential/transaction log backup files. However, there is a lag time of a few days before each tape arrives offsite.

In the event of a natural disaster (fire, flood, etc.) I would be at the mercy of whatever happens to be the most recent offsite network tape backup. So I clearly need to make some improvements in that regard. Otherwise, I think I'm in good shape for other types of DR scenarios. (server failure, disk corruption, human error, etc).

I would advise you to routinely test your backup & recovery.

You may think that you can recover but when a disaster strikes you may find that you do not have the backups that you need.

A single point of failure is all that it takes.

I would suggest that you have a disaster recovery environment that mimics your production environment.

You should test this repeatedly. You will probably find issues.

Once you feel that you have perfected it you should continue to test it on a routine basis for it is better to be safe than sorry. You would be surprised what can go wrong.

Also be cautious when you have to depend on someone else to backup your file to disk, tape or whatever.

If something goes wrong you will take the hit and it is your job that is at stake!

I'm on board with Welsh Corgi, make sure you're testing your backups.

Other than that, it seems like a reasonable plan. I'd just run the log backups for 24 hours though. Why try to differentiate between business & non-business hours? One guy working late on some data entry, no log backups... disaster.

Good point on the Transaction Log Backups.

I worked at a place where I found that they were truncating the transaction log backups and sending an Alert when they became full.:hehe:

Also another important consideration is how long to archive you tape backups.

There are business and legal decisions that impact on this. You may want to retain Tapes for the End of Month for so many years or just after the closing for the end of month. That is in addition to retaining the nightly backups.

On many occasions you do not find out for 6 weeks or so that someone deleted something, the business wants it recovered but you do not have the backup to recover the data.

If you use a service to store your tapes, carefully read the terms of service before executing a contract and hold that company to the terms. I needed a backup from tape and it was a nightmare trying to get the backup tape. A congressional would have required less effort.

Tape backups are great but if you are not doing the backs yourself make sure that you review the Veritas Logs to ensure that the backups are complete. You would be surprised how often this process fails.

Tape backups should be stored off-site and you should be able to recover them quickly in the event they are needed.

Gail published a very nice article on managing transaction logs ...

http://qa.sqlservercentral.com/articles/64582/

In general we use the same methode as Brad described in his reply.

I have to agree that Brad's response was excellent

Gail's article was excellent as expected.