This article will focus on tape backup solutions and rotations, simply because it is the most prevalent.

When it comes to tape backup, everyone used to use DAT tapes.  But, DLT tapes are becoming increasingly preferred due to speed and capacity.

DAT tapes are your standard 4mm tapes that have a capacity of 4GB uncompressed and 8 GB compressed.  The best manufacturers that I have worked with for DAT drives are Compaq, Seagate, and HP.  Because DAT technology is so old and stable, just about any manufacturer's drive should give you years of good use.

DLT tapes are much larger, but hold significantly more data.  There will generally come in one of three flavors: 15/30, 20/40, and 35/70.  For the price difference, I generally go with 35/70 drives.  I also normally buy HP for DLT drives just from preference and past experience.  What the DLT gives me is the ability to store the equivalent of almost 9 DAT tapes on a single DLT tape.  This makes backup plans much more simple, because in most installations you can generally fit an entire database or server on a single tape.

Regardless of the type of media you get, the most important thing is planning your tape rotation is how many tapes you are going to need.  Are you only doing full backups?   Are you dumping transaction logs?  How frequently does the data need to be dumped?  What is the volume of data that needs to be dumped?

Many people utilize a 14 day tape rotation in their backup plan.  This was the plan I used to implement until I ran across someone who suggested a much simpler method.   I use that and recommend it everywhere I go.  (I forget the person who originally suggested it otherwise I'd mention him here.)  I call this my "brain dead" tape rotation.  This does take a lot of tapes, but it is very simple and a child can follow it.  I use a 31 tape rotation.  If each backup requires 2 tapes, then I use 62 tapes.  If you need one tape for full backups and 1 tape for tran logs, then I use 62 tapes.  The idea is very simple.  If the dump occurs on the 1st of the month, you use tape #1.  If it occurs on the 15th, you use tape #15.   If there isn't a 29th, 30th, or 31st in that month, then that tape simply gets skipped.  I also try to design the backup plan such that the entire server gets dumped every day.  You can't get any easier than this.  Now when you need a tape to restore a database or server, you only have to know what day of the month it is.

Now, you might say that this can be a lot of tapes.  Yes, it can.  But, the argument to that is what do you want to have happen when a server crashes?  You can cut a few hundred dollars in tapes and make the tape tracking much more difficult, possibly have a bad tape, and potentially have a bad restore.  When a server crashes, things get very stressful really quickly.  The entire place is controlled chaos with tension you can cut with a knife, if you are lucky.  The last thing you want to be forced to do is figure out which tape you need.  With this method, all you have to remember is the day of the month.  It also has the advantage that a 2 year old can follow it.  This means you don't need a DBA overseeing the tape rotations and that function can be offloaded to someone else.  (That is not to mean I am drawing a comparision between a 2 year old and the person rotating your tapes.)  The DBA is then free to perform the rest of the duties and an operator can take care of the tape rotation.

In addition to that, I use offsite storage and strongly recommend doing this.   What offsite storage gains you is that the entire building can blow up leaving nothing but a pile of rubble, but you can still bring up a server, restore the data, and be back in business with data as of the last backup.

Before I get to rotations, I also want to mention that I do dual simultaneous backups whenever feasible.  This means that when I dump databases and tran logs, I dump to both disk and to tape.  This gains me media protection.  The disk or disk sector could go bad and I still have a tape to work from or vice versa.  The last thing you want to do is dump to just the tape, have the server crash, and start a restore only to find that the tape is bad.  Try explaining that one to the customer whose $100 million transaction you just lost.  After the backup is done, I use another tape to backup the disk dump to tape.  This gives me a dump directly to the tape, a dump residing on disk, and a disk dump on a tape.  That way I actually have three copies of each dump.  The odds are incredibly remote that a disk and two tapes will simultaneously fail at the same time a server crashes. (I use specific tools to perform the dumps, but that will be covered in another article.)

The rotation I use is that the oldest 5 tapes and the last tape dump (copy of the disk dump only) are on site with all others being rotated offsite.  This means that on site you have the most recent backup sitting on disk somewhere and a copy of that on a tape.  The most recent dump you did directly to tape has been rotated offsite so that you have a copy of the most recent backup offsite in case the building blows up.   This means that if a database corrupts, you already have a dump loaded on the server for an immediate restore.  If that one happens to be bad, you have a copy of it on tape.  If the tape also happens to be bad, you have another one in offsite storage.  (Offsite storage selection will also be a separate article.)

To recap on this, I use a 31 tape (times however many tapes per day are needed) backup set.  The day of the backup corresponds to the number on the tape.  I simultaneously dump to both disk and tape to give me protection from media failure.   The disk dump is also backed up to an additional tape.  All tapes are stored offsite with the exception of the oldest 5 tapes and the most recent copy of the disk dump on tape.