Developing and Executing a Backup Plan

SQL Server 2005 provides two methods for planning and executing backups. You can either use the graphical interface of Management Studio or the T-SQL backup commands. We'll cover both of these methods here, starting with Management Studio.

SQL Server 2005 Management Studio

SQL Server 2005 Management Studio exposes backup management capabilities for a DBA to either develop a scheduled maintenance plan or directly perform a backup. Before you start, decide the destination for the backup image. It can be a backup location such as a directory path with a filename or a backup device.

If you're using a backup device, first you'll need to create a logical device that defines where SQL Server will copy the backup image. From SQL Server 2005 Management Studio, go to Server Objects, choose Backup Devices, and then New Backup Device. You'll see the dialog box shown in Figure 18-2. There are two destination choices.

• Tape: Requires that a local tape drive be present on the database server

• File: Requires a valid disk destination

Figure 18-2

To perform database backup from SQL Server 2005 Management Studio, select the database you want to back up, right-click, and choose TasksBackup. The Backup Database dialog box appears, as shown in Figure 18-3.

Figure 18-3

In the Source area of this dialog, configure the following:

• Database: Choose the database to back up.

• Recovery model: This value is grayed out, as it cannot be changed. Notice that this is in full recovery model. If it was simple recovery model, the transaction log could not be backed up, as the transaction log is truncated by the checkpoint process and files, and filegroups backups would not be available, except for read-only files or filegroups.

• Backup type: Choose among Full, Differential, or Transaction Log.

• Backup component: Choose from these options:

  • Database: Choose to backup the database.

  • Files and filegroups: Choose to backup files or filegroups. This option will present a dialog box where you can choose one or more files or filegroups.

In the Backup set area of this dialog, configure the following:

• Name: Give the backup set a name for easier identification. This name will distinguish the backup from others in the backup device.

• Description: Provide an optional description for this media set.

• Backup set will expire: Configure these options based on you're business's retention policy; this protects from SQL Server's backup process overwriting the backup set.

  • After: Determines the number of days, from 0 to 99,999, after which the set can be overwritten. Zero is the default, which means the set never expires. You can change the server-wide default by choosing SQL Server PropertiesDatabase Settings. Change the Default backup media retention (in days).

  • On: Specify a date on which the backup set will expire.

SQL Server 2005 supports up to 64 backup devices. In the Destination area of this dialog, configure the following:

• Disk: Specify a full valid destination path with filename or a disk backup device.

• Tape: Specify a tape drive or a tape backup device. The tape drive must be local to the database server.

Note

Clicking the Contents button shows the media set or media family of the device selected.

While in the Back Up Database dialog box, select Options to see the dialog box shown in Figure 18-4.

Figure 18-4

In the Overwrite media section, you can choose to back up to the existing media set, in which case, you have to configure these options:

• Append to the existing backup set: Preserves the existing backups by appending to that media set. It is the default.

• Overwrite all existing backup sets: Erases all the existing backups and replaces them with the current backup. It will overwrite all existing backup sets unless the "Check media set name and backup set expiration" box has been checked.

Or you can choose to back up to a new media set and erase all existing backup sets, which erases all backups in the media and begins a media set, according to your specifications.

The Reliability section of this dialog has two checkboxes that are both good recommended practices, as a backup is of no value if it is not recoverable.

• Verify backup when finished: After the backup finishes, SQL Server checks that all volumes are readable.

• Perform checksum before writing to media: SQL Server does a checksum prior to writing to media, which can be used during recovery to determine that the backup was not tampered with. However, there is a performance penalty with this operation.

The Transaction Log section of this dialog contains options that only apply during transaction log backups.

• Truncate the transaction log: During normal transaction-log backups, it is common practice to manage the size of the transaction log and to truncate it after it has been backed up to a backup media.

• Back up the tail transaction log and leave the database in the restoring state: This option is useful when the data files of the database are not accessible (for example, if the physical drives have failed but the transaction log in separate physical drives is still accessible). As a result, during database recovery, apply this as the last transaction-log backup to recover right to the point of failure.

The Tape Drive section of the dialog contains tape drive instructions to rewind and unload the tape. Then click OK and the backup process executes.

A better approach to executing the backup plan is to develop maintenance plans for each database, schedule them, and have SQL Server e-mail you a backup history report. To create maintenance plans for one or more databases, from SQL Server 2005 Management Studio, choose the Management folder, then Maintenance Plans, and then right-click and choose New Maintenance Plan. Then you'll be asked to name the maintenance plan and taken to the maintenance plan design screen.

1. Choose the Back Up Database Task and drag it to the designer.

2. Right-click on the Back Up Database Task to open the Properties, which are shown in Figure 18-5.

3. In the Connection field, choose local server connection, or, if this maintenance plan is to back up databases on another server, choose New Connection and provide the connection information.

4. In the Databases field, choose one or more databases. You can choose more than one database if they have identical backup requirements.

5. In the Backup Component field, choose either Database or Files and Filegroups backup. If you choose Files and Filegroups, you'll need to choose which Files or Filegroups to backup.

6. In the Destination field, choose either disk or tape. Click the Add button to configure the backup location. For disk, provide the full path to the filename or the disk backup device. For tape, provide the tape location or the tape backup device. Additionally, you can use more than one file or backup device. If more than one is chosen, all the databases will be backed up across them, up to the 64 backup devices that SQL Server supports

7. On the If backup files exist field, select whether to append to the existing backup file or to overwrite; the default is Append.

8. To backup to a disk where each database is on its own file, choose Create a backup file for every database option. You can also choose to place each database backup in its own subdirectory.

9. Click the Verify backup integrity checkbox as a recommended practice.

10. Click OK.

11. Click the Logging button and choose how to receive the backup history report. If you choose e-mail, Database Mail must be configured. Moreover, an Agent Operator must be configured to e-mail the report. Then click OK.

12. Click the Schedule button and set up the schedule for this maintenance plan.

13. You can include additional back up database tasks for other database backups with various backup requirements. For example, one Back Up Database Task may be performing full database backups on several databases, but another may be performing differential backups, while another may be performing filegroup backup. They will share the same schedule.

Figure 18-5

When the maintenance plan is complete, it will be automatically scheduled as a SQL job into SQL Agent.

Transact-SQL Backup Command

All the backup commands using SQL Server 2005 Management Studio and all functionality are available directly using T-SQL. Here are some examples of the syntax:

1. Create a logical backup device for the AdventureWorks database backup:

    EXEC sp_addumpdevice 'disk', 'AdventureWorksBackup', 'F:\BACKUP\AdventureWorks.bak'; 

2. Create a full AdventureWorks database backup:

    BACKUP DATABASE AdventureWorks TO AdventureWorksBackup; 

3. Create a full differential backup:

    BACKUP DATABASE AdventureWorks TO AdventureWorksBackup WITH DIFFERENTIAL; 

4. Create a tail transaction-log backup, during a database failure when the data files are not accessible but the transaction log is.

    BACKUP LOG AdventureWorks TO tailLogBackup WITH NORECOVERY;