SQL Server Backup by Design

I included this section to help you understand management schemes and regimens that need to be built to support service level requirements…starting with some basic backup/restore concepts.

Practicing Scratch and Save

SQL Server 2005 does not support the concept of scratch and save sets directly, but it provides the facility, and you can code this logic into your T-SQL scripts. This is something you have to design manually. Remember that it is not really up to SQL Server to enforce how you manage your backup strategy. The DBMS only gives you the tools to perform a backup and to restore data, and to manage your media according to a scheme you devise. It is worth mentioning what a scratch and save set is because you should understand the terms for more advanced, sophisticated database backup procedures.

A save set is a set of media in the media pool that cannot be overwritten for a certain period of time, and a scratch set is a set of media that is safe to overwrite. A backup set should be stored and cataloged in a save set for any period of time during which the media do not get used for backup. You can create your own database in SQL Server that provides rotation information into and out of scratch and save sets.

The idea behind the scratch and save approach is to protect data from being overwritten for predetermined periods, according to your backup plan. For example, a monthly save set is saved for a month, while a yearly set is saved for a year. Later when the “safe” period of time has expired, you can move the save set to the scratch set and target it for overwriting.

Once a set is moved out of the save status into the scratch status, you are tacitly allowing the data on it to be overwritten, essentially destroying it. A save set becomes a scratch set when you are sure, through proper media pool management, that other media in the pool contain both full and modified current and past files of your data, and it is safe to destroy the data on the scratch media.

Save and scratch sets enable you to ensure that your media can be safely recycled. If you do not manage your media according to a scratch and save routine or schedule, you make every set a save set, which means you never recycle the tapes. Recycling will risk tapes, because if they will be constantly used they will stretch and wear out a lot sooner.

Rotation Schemes

A rotation scheme is a plan or system you use to rotate the media in your backup sets. At the simplest level, a rotation scheme is a daily or weekly backup using one cartridge. This is not much of a DR scheme, because you would be writing over the media every day So in order to determine the best rotation scheme, you need to consider what you are doing with the backups: Are you just archiving, doing version control, backing up a state, or managing a disaster recovery regimen?

From this description, you can deduce that the three main reasons for backups are archives, version control, and recovery. Data in an archival state, or required for analysis, need not be located on-site and is kept for record-keeping, reporting, and decision support; data in a version control period is stored off- and on-site for access to full weekly generations of the data, and to be used to restore the databases to certain points in time. Data in the recovery period is stored both on- and off-site and is either online or very near to the server.

Understanding Rotation Schemes

Let us now expand our rotation scheme. The first option would be to rotate the media every other day, so that you could be backing up to one tape while the alternate is in safekeeping somewhere. If the worst were to happen, a tape gets eaten by the device or something less common, you would still have a backup from the previous day If the machine were stolen, you would be able to restore it. But rotating every other day is only useful in terms of total data loss. So you have a full backup of all your databases, every day. What about wear and tear? A tape or a platter is a delicate device. Inserting and removing it every other day and writing to it over and over can put your data at risk. Tapes do stretch, and they get stuck in tape drives. Tapes should be saved according to the scratch and save discussion earlier in this chapter.

And then what about version control? Rotating with multiple media, say a week’s worth, would ensure that you could roll back to previous states of a database or to points in time. We could refer to such a concept of versioning as a “generation system” of rotation (not sufficient for critical restore, however). In fact, one such standard generation scheme is widely used by the most seasoned of backup administrators to achieve both the ideals described-versioning and protecting media from wear and tear and loss. It is known as the GFS system, or Grandfather, Father, Son system.

Let’s create a GFS scheme to run under SQL Server 2005 Backup. Most high-end backup software can create and manage a rotation scheme for you, but for now you will need a legal pad. So now let’s put a label on one of our tapes or disks and call it Full, or First Backup, or Normal # 1-whatever designates a complete backup of the system and collection of files and folders.

The first backup of any system is always a full backup, and the reason is simple. Backup, and you, need a catalog or history of all the files in the backup list so that you can access every file for a restore and so that Backup can perform differential analysis on the media. Do your backups according to the procedures we discussed earlier. You should have had enough practice by now. And you are ready to go from a development or trial backup to a production rotation scheme.

As soon as you have made a full backup set, label the members as discussed and then perform a second full backup (or copy the first). On the first backup set, add the following information to the label:

• Full_First: January 2006

• Retention: G (which stands for Grandfather) or one year, dd-January-2006

• Serial number: Choose one, or write some SQL code to generate it

On the second set, add the following information to your labels:

• Full_First: Week1-January 2006

• Retention: F (which stands for Father) or one month, Week1-February-2006

• Serial number: Choose one, or write some SQL code to generate it

On the next day, you need to choose a second set of media, but this time only the files that have been changed will be backed up using the differential option. Let’s say we are doing differential transaction log or database backups for example’s sake.

On the differential set, add the following information to the label:

• I_First (or a day of the week): Mon, or First

• Retention: Seven days or every Monday

• Serial number: Choose one, or write some SQL code to generate it

On the next day, put in a new backup set and perform the next day’s differential. This time, the label information is Tues or “Second.” Then retain these media in a seven-day save set and store them in a safe place. On Wednesday, perform the third differential and on Thursday, perform the fourth differential. Let’s now look at what we are achieving:

We have created a Grandfather set that we store for a year. If we started this system in January 2006, we will not reuse these tapes until January 2007; the retention period is one year; these are the oldest saved versions of the databases you will have.

The second copy set is the Father set of the scheme, and this set gets reused in four weeks’ time. In other words: Every four weeks, the set can be overwritten. This does not mean that we only make a full backup once a month. On the contrary: Notice that we made one full set and four differential sets, so we are making a full backup once a week and four “diffs” Monday to Thursday. We only retain the weekly set for a month, meaning that at the end of each month you will have five full backup sets, one set for each week, retained for a month, and one set for each month, retained for a year.

What about the differential sets? These sets are the grandchildren of our rotation scheme. We save them for seven days and return them for scratching on the same day the following week. So what we back up on Monday gets written over next Monday, Tuesday gets written over on Tuesday, and so on. This also means that at any given time, your people can access the previous day’s data, the previous week’s data, the previous month’s data, and the previous year’s data. What we have created here is a traditional rotation scheme for performing safe and accessible backups of SQL Server databases, for low-end OLTP systems typical of most businesses.

There are variations on this theme, and you will need more than just seven of whatever media you are using if you apply the schemes to transaction log backups. For example, for the full GFS rotation, you would need the following for a single server that used one DLT tape drive:

• Daily backups (differential, rotated weekly): 4+

• Weekly Full (rotated monthly): 4+

• Monthly Full (rotated annually): 12+

• Total tapes: 20+

The best days for such a rotation scheme are Monday-Thursday (differential) and Friday (full). Even on big systems, you’re unlikely to be doing a differential into the following day. And on Friday, you have the whole day and the weekend to do the full backup, at a time when the system is most idle. You could start after the last person leaves on a Friday and you would still have about 48 hours of backup time to play with; besides, the databases in most businesses are unlikely to be used over the weekends or holidays.