Before we can move on their is a second half to deciding where to backup…choosing a tape, or file for that matter, rotation. If you used a new tape for each day’s backup you would probably eat up a good part of your budget on nothing but tapes. In order to save money a few popular tape rotation schemes are in use. The rotation we are going to look at is known as the Grandfather-Father-Son rotation.
In a Grandfather-Father-Son rotation you start out by using a new tape for each day of the week for the first week. For each week following the first week you reuse the same tapes except for the last tape of the week. By using a new tape at the end of each week you can keep an archive of data. In the event you need to restore data that was deleted or lost, the archive from past weeks is available. Once a month has gone by you keep the tape for the last week of that month and then reuse the end-of-week tapes. Here is what a Grandfather-Father-Son rotation would look like for our example over a two month period:
11 Tapes are used: M, T, W, TH, F, S, W1, W2, W3, Month1, Month2…
In our example we must take the monthly backup on Sunday because that is the only day we make a full backup. However, if you make a full backup of your database every day of the week you can use the monthly tape on the last day of the month no matter what day of the week it ends on. To illustrate, this is what a Grandfather-Father-Son rotation would look like if we took a full backup every night for the next two months:
Note that a new tape is substituted for the last day of each month. Also note that one the last day of the month has passed the end-of-week tapes can then be reused.
Having decided on how often to backup and where to backup, you now must choose a location to store your tapes. Deciding on a location is going to depend greatly on your situation, but there are some general rules you should keep in mind. First of all the location, either on or off-site, should be secure! If someone has access to your tapes…you mite as well give them access to your server. Second you need to find a balance between keeping the most recent tapes nearby (incase you need to restore a database) vs. the need to store tapes off-site (incase of a disaster). One approach that we talked about earlier is storing a file backup on a second computer from your SQL Server. By doing this not only can you recover from a crash faster, but it also allows you to store tapes off site without having to worry about going to get them to restore a database. You can also accomplish this same effect, minus the faster restore time, by making two sets of tape backups; one off-site for storage and one on-site for quick access. You may even consider making a third copy of your monthly backup for storage in a third off-site location. Also keep in mind that you don’t have to keep all your tapes off-site. Depending on your needs you may find that keeping the weekly backups, or even the monthly backups, off-site is adequate.