Table of Contents

Chapter 11

Backing Up Databases

Certification Objectives *

From the Classroom *

Restoration Time *

Choosing the Appropriate Backup and Restore Strategy *

Database, Transaction Log, File and Filegroups *

Differential Backup *

From the Classroom *

Restoration Plan *

Developing a Data Availability Solution *

Using a Standby Server *

Using Clustering *

Disk Devices *

Tape Devices *

Named-Pipe Devices *

Physical and Logical Devices *

Performing a Database Backup *

Interrupted Backup Operations *

Performing a Transaction Log Backup *

Performing a Differential Backup *

Performing a File or Filegroup Backup *

Backing up the master, model, msdb, and distribution Databases *

master Database *

model, msdb and distribution Databases *

Certification Objectives

What do you think would happen if employees in your company were not able to access a mission critical database? In most cases, the least that would happen is a loss of revenue. If the database cannot be recovered in a timely manner, then your company may go out of business! While this may seem extreme, it can happen. Since databases are so vital to businesses, it is imperative that a plan to back up databases is developed and implemented.

In the first section of this chapter we will discuss the importance of preventing data loss. We then continue by discussing different backup and restoration options that are available to you. In the third section of the chapter we discuss planning to back up your data. In the fourth section we then actually implement the backups. The Database Maintenance Plan Wizard is another option available for backing up your databases; it is discussed in the fifth section. We close out the chapter with a discussion on checking database integrity.

Preventing Data Loss

As you can imagine, losing a mission critical database can be very detrimental to your company. There are several factors that can lead to the loss of data in a database. These include:

Backup and Restore

As you can see, to prevent data loss it is very important to have a current copy of any database that your company cannot do without, as there are several negative actions that can occur to databases. There are many different methods, however, of completing backups and restores on your databases.

From the Classroom

Restoration Time

Obtain from your key users in writing the length of time that it is acceptable for the database to be unavailable. For example, if you are told that four hours is acceptable, then you must develop a schedule such as the following example illustrates.

Time Action
1 hr Try to recover server without using backup (Drive to the office, restarting services, restarting the server, etc)
30 min Retrieve tape from off-site storage (Is it open 24 hours per day?)
2hr 30 min Restore 8 GB of data from tape. (Remember to test your restoration speed -- the speeds given by tape manufactures may be much faster than what you achieve in your environment.).

—David Smith, MCSE + Internet

Choosing the Appropriate Backup and Restore Strategy

The backup and restore strategy that you pick for SQL Server 7.0 in your company is ultimately determined by the needs of your company. Let’s discuss the backup and restore capabilities that exist in SQL Server 7.0.

Database, Transaction Log, File and Filegroups

The act of backing up a database copies everything in the database, including any needed portions of the transaction log. As discussed in Chapter 1, the transaction log is a serial record of all the modifications that have occurred in the database, as well as which transaction performed each of the modifications.

It is also possible to backup only the transaction log of a database. When a transaction log is backed up, it only backs up the changes that have occurred in the transaction log from the last back up.

Other SQL Server 7.0 items that can be backed up are files or filegroups, as well as combinations of files and filegroups. Normally, a file or filegroup backup is created only if there is not enough time to create a complete backup of the entire database. For example, you might for some reason have only three hours per night to perform a backup of a database that takes six hours to complete. If this is so, then you can backup half of the files one night and the other half of the files the next night.

On the Job: You can also use a backed up database for other purposes besides system problems. For example, it may become necessary for you to move a database to a new server. Backing up a database from the old computer, and restoring the database to the new computer can be a quick method to accomplish this feat.

Restoring a database from a backup returns the database to the same condition it was in when the backup was created. To guarantee that the database remains in a consistent state, all incomplete transactions in the database backup are rolled back (undone).

Since the transaction log can be backed up separately from the database, it can also be restored separately from the database. Restoring a transaction log from a backup reapplies all completed transactions that are in the transaction log to the database. SQL Server 7.0 reads through the transaction log and rolls forward the transactions. When it reaches the end of the transaction log, the database will be in the exact state it was when the backup operation started. After it completes rolling forward, the restore operation rolls back all transactions that were incomplete when the backup operation started.

To speed up recovery of your database, you can restore only the damaged files or filegroups. This is very handy for large databases in time-critical environments. Individual files or filegroups can be restored from either a database backup or a filegroup backup. Figure 11-1 shows the filegroups available from a database backup that was performed on the Northwind database. So even if you only create database and transaction log backups, you still have the capability to restore individual files or filegroups from the database backups.

Figure 1: Filegroups Available for Restoration from a Database Backup

Exam Watch: If you use file and filegroup backups then you must also use transaction log backups so that when you perform a restore procedure you can bring the database to a consistent state.

Differential Backup

Another item you need to consider for your backup and restore strategy is the differential backup. The differential backup can increase the speed of your backup operations by being performed in addition to the use of database backups. A differential database backup only backs up data changes that have been made to the database since the last full database backup. Since the differential database only backs up changes, it is smaller and takes less time to complete than a full database backup. As you can imagine, frequently creating differential database backups can greatly reduce the amount of data you risk losing. Differential database backups, unlike transaction log backups, do not allow a database to be restored to the exact point of failure. Differential database backups only allow restoration to the point in time that the differential database backup was first created. There are several reasons that you may consider using differential database backups. Two of the reasons include:

Depending upon your needs, you may decide to implement a combination of database, differential database, and transaction log backups. By using a combination of these three different backup mechanisms, you can significantly minimize recovery time and the amount of potential data loss due to failure. Let’s look at an example of using these three different methods of backup.

  1. Completely back up the database every night at 11:00.
  2. Create a differential database backup every three hours during the business day.
  3. Create transaction log backups in between each of the differential database backups. Set the transaction log backups to be performed 90 minutes after each differential database backup.

To restore a database that has been backed up using the combination of database, differential database, and transaction log backups, you use the following sequence:

  1. Restore the current database backup.
  2. Restore the last differential database backup.
  3. Restore all of the transaction log backups that have been created since the last differential database backup was accomplished.

From the Classroom

Restoration Plan

Almost all SQL Servers are backed up on a regular basis, but this is only half of what you need in order to be prepared for a disaster. You must also prepare by writing, testing, and practicing your restoration plan. If you are properly prepared, you will be able to carefully follow your restoration plan even though hundreds of your users can not access their data. Do not underestimate the stress of watching your tape drive light blink on and off as data is being restored. Your restoration plan should include the two topics that follow.

Restoration Hardware Most database restorations are necessitated due to hardware failure. Therefore, your plan should include provision for a computer that has enough disk space and RAM. Remember to plan for an additional tape drive. You will not have enough time to find your tool kit, an extra hard drive, and some RAM when your database is down.

Practice Restores Even once your plans for restoring your database are complete, a practice restore will give you confidence that nothing has been missed. If you have a rotation of staff who is responsible for restoring a server, insist that they practice a restore.

David Smith, MCSE + Internet

Developing a Data Availability Solution

Using a combination of database, differential database, and transaction log backups can significantly minimize the amount of downtime experienced for your databases. You may have databases, however, that are so mission critical that any downtime at all is fatal to your business. In this case, you need another data availability solution such as a standby server or clustering.

Using a Standby Server

A standby server is a second server that can be brought on line in the event of failure of the primary server. The standby server contains an identical copy of the databases on the primary server. To copy the databases to the standby server, you can easily conduct database backups on the primary server and restore the databases on the standby server. To make sure that the databases on the standby server remain synchronized to the primary server, you restore transaction log backups from the databases on the primary server to the standby server. If the primary server (or even if just a single database) fails, the databases on the standby server are immediately ready and available to users. Any user processes that cannot access the primary server should use the standby server instead. It is important to note that users are not automatically switched over to the standby server. All users need to log into the standby server and restart any tasks they were performing when the primary server became unavailable. Figure 11-2 shows that the standby server has a different network name and address than the primary server.

Fig11-02.gif (32540 bytes)

Figure 2: Primary Server and Standby Server with Different Names and Addresses

Even though you use transaction log backups to keep the primary server and standby server in synchronization, it is very doubtful that they will be in complete synchronization. It is inevitable that some changes to the databases on the primary server have occurred since the transaction logs on those databases were backed up. It is possible, however, for you to synchronize the primary server with the standby server, as long as the transaction log on the primary server is not damaged. In this situation, it doesn’t matter if the database is not accessible. Make a backup of the transaction log on the primary server and then restore it onto the standby server. This will allow users to work with an exact copy of the primary database immediately prior to failure. The only exception is that any non-committed transactions have been lost forever.

As you can see, using a standby server allows users to continue working with databases even though the primary server is not available because of problems. Of course a standby server can also be used when a primary server needs to have a hardware or software upgrade performed on it. Don’t forget that when you bring the primary server back online, there are things to do. For instance, you will still need to synchronize the databases that have been modified on the standby server to the databases on the primary server prior to allowing user interface with it.

Using a standby server also requires lower bandwidth requirements than clustering. We discuss using clustering in the next section.

Using Clustering

As I already mentioned, a standby server has a different network name and address than the primary server. If you need to make use of failover support where the network names and addresses are automatically maintained, then you need to use clustering. SQL Server 7.0 includes Failover Support, but it must be used in conjunction with Microsoft Cluster Server (MSCS). MSCS is included in Windows NT Server Enterprise Edition and allows a cluster of two servers. Unlike the standby server configuration, clustering consists of several components as shown in Figure 11-3.

Let’s take a closer look at the components involved in clustering. As with using a standby server, clustering uses two physical servers. Each of the physical servers in the cluster, however, is available at all times to do work and take over the workload of the other server in the cluster. A network connection between each server detects whether the other server is available or not. The hard drive on each server contains Windows NT Enterprise Edition and MSCS. SQL Server 7.0 does not exist on the physical servers, it is located on one or more shared drives. There is also one or more SQL Server virtual servers. A SQL Server virtual server has a network name, IP address, disk array, peripherals, and services. Clients connect to a SQL Server virtual server using the virtual server name and not the physical Windows NT Server name. The clients are not aware of which physical server actually has control of SQL Server. If a failure occurs, then control of all resources and services are taken over by the remaining functioning server. The clients will never be aware that a failure occurred.

Fig11-03.gif (56716 bytes)

Figure 3: SQL Server 7.0 in a Single Virtual Server Clustering Environment

The type of failover configuration dictates how many copies of SQL Server 7.0 is loaded on the shared drive. There are two main types of failover support that SQL Server 7.0 can be configured for as shown in Table 11-1.

Type of Failover Support Configuration
Active/Passive One virtual server, one copy of SQL Server 7.0 on shared drive, and two physical servers. The active server controls SQL Server. The secondary server does not take control of SQL Server until the primary server fails or an administrator changes the control of SQL Server to the secondary server. This configuration provides the maximum availability performance for your resources.
Active/Active Two virtual servers, two copies of SQL Server 7.0 on shared drive, and two physical servers. They are setup so that each of the two virtual server configurations are reverse arrangements of each other. Each physical server controls one SQL Server virtual server, while the other physical server is the secondary server. Figure 11-4 shows an example of the Active/Active failover configuration.

Table 1: Configuration for the Types of Failover Support Available in SQL Server 7.0

Fig11-04.gif (69588 bytes)

Figure 4: SQL Server 7.0 in an Active/Active Virtual Server Clustering Environment

Even though SQL Server Failover Support runs on top of MSCS, it is controlled through the MSCS Cluster Administrator. The MSCS Cluster Administrator gives you the capability to control several parameters, including failover and failback policies, as well as moving services and resources between the servers. When the primary server fails, services automatically fail over to the secondary server. All uncommitted transactions are rolled back in preparation for the clients to reconnect to the database.

Exam Watch: During a failover, SQL Server Failover Support may not function correctly if it runs short of memory. To ensure the failover is successful and that the clients have satisfactorily response times, make sure you have sufficient memory for your cluster.

Planning to Back Up Your Data

We have now discussed how data loss occurs as well as different backup and restore strategies. Next it is time to learn about the planning that is necessary to back up your data. It can be very detrimental to your databases, not to mention your job, if backups are not planned appropriately. The plan you implement is, for the most part, driven by the requirements in your company for the availability of data. There are several items that you need to consider when planning to back up your data.

The first item you need to think about are the types of failures that could affect your databases. Everyone needs to consider hardware faults and fire as types of failures. Despite that, other failures may be specific to your location. For example, if you live on the Mississippi Gulf Coast then you need to include the possibility of hurricanes and flooding in your plan.

Plan a backup procedure based on the importance of the type of database your business uses. For critical data, make sure that you can recover to the point the failure occurred. For less critical data, you may only need to restore from the last database backup that you have. Include in your backup plan the importance of time in getting the database back online. If the database must be back online immediately, then you may need to use a standby server or clustering.

Perform tests of the backup and restore plan. Verify that the procedures work within the time limits and data availability constraints set by company policy. It is very important to perform tests while in a controlled environment. When the server outage occurs for real, it will be an extremely tense situation.

The time it takes you to recover from a server outage and minimize the amount of lost data in a database is contingent on the amount of time that has elapsed between backups. The optimal amount of time between backups is the time interval that results in the best balance of two contrasting requirements:

There are several items you need to include in your backup plan. These consist of:

This may sound like it is a lot of work for you to accomplish. It is very important that you have a plan in place long before it is needed! We will discuss the SQL Server Database Maintenance Plan Wizard later in this chapter. It can help you to create and automate a backup plan.

Implementing Backups

As we have discussed earlier in the chapter, there are several different backup strategies available for backing up the data in SQL Server 7.0. In this section we will discuss performing various backups of your data, and also give you a chance to practice performing backups. All backups performed by SQL Server 7.0 must be backed up to a backup device. SQL Server 7.0 supports three different types of backup devices: disk, tape, and named-pipe devices.

Disk Devices

Disk backup devices are files on hard disk drives and are the same as normal operating system files. Disk backup devices can be defined on a server’s local disk drive or on a remotely located disk drive on a shared network system. Disk backup devices can be as big or as small as necessary with the maximum file size equivalent to the free disk space that is available. Figure 11-5 shows a backup device named Northwind.BAK located in the D:\MSSL7\BACKUP folder.

fig 11-5.gif (15445 bytes)

Figure 5: A Disk Backup Device Named Northwind.BAK

If you are performing the backup over the network to a disk drive on a remote computer, then certain components need to be in place. You need to use the universal naming convention name (UNC) in the form \\Servername\Sharename\Path\File or a locally redirected drive letter, to specify the location of the file. Since errors can be encountered when backing up data over a network, make sure that you verify the backup. We will discuss database integrity later in the chapter.

Even though it is possible to perform a backup operation to a file on the same physical disk drive as the database, it is not recommended. This makes a lot of sense if the disk drive that contains the database has a head crash or some other failure. If the backup is located on the same drive, then you have no method of restoring the database!

Tape Devices

Tape backup devices are used in exactly the same way as disk devices with two exceptions:

Figure 6: Tape Backup Device Not Available in the Backup Device Properties Window

Named-Pipe Devices

Named-pipe backup devices are available so that third-party vendors can connect their own software and provide specialized backup capabilities. To use a named pipe backup device, you must use a Transact-SQL statement that includes the name of the named pipe that will be used by a client application.

Physical and Logical Devices

SQL Server can identify backup devices using either physical or logical device names. Either type of name can be used interchangeably when you back up a database.

A physical backup device is the name used by the operating system to identify the backup device, for example, D:\MSSQL7\BACKUP\Northwind.BAK. A logical backup device is nothing more than an alias used to identify the physical backup device. The logical device name is permanently stored in the system tables within SQL Server 7.0. Why would you want to use the logical device name since you already know the physical device name? Simplicity is the answer. It’s much easier to use the logical device name NW_Backup instead of the physical device name of D:\MSSQL7\BACKUP\Northwind.BAK.

Performing a Database Backup

Performing a database backup allows the database to be restored to the exact state it was in when the backup operation was completed. Any modification made to the database after the backup operation completed cannot be recovered. When you backup a database, all of the data is backed up whether or not it has changed since the last database backup.

Performing a database backup means that it does not need to rely on any other type of backup to be restored. It does mean, however, that the database backup uses more storage space per backup. It also needs more time to complete the backup procedure when compared to other backup types that are available.

When creating database backups, Microsoft recommends that the transaction log be set to automatically truncate every time a checkpoint occurs in the database. This can be accomplished by placing a checkmark in the box to the left of Truncate Log on Checkpoint from the database options tab in SQL Server Enterprise Manager. This helps prevent that transaction log from becoming full.

It is important to note that the backup operation copies only the data in the database to the backup file and not any unused space in the database. Since the database backup only contains the actual data in the database, it is probably going to be smaller than the database itself.

Backup operations in SQL Server 7.0 can occur while the database is online and in use. There are four operations, however, that are not allowed during a database backup:

Exam Watch: If you start a backup when one of these operations is already in progress then the backup aborts. If a backup, however, is already in progress and you attempt one of these operations, then the operation fails and the backup continues.

There are three methods available to create a database backup that we will discuss in this section; the Create Backup Wizard, Transact-SQL, and SQL Server Enterprise Manager. The Create Backup Wizard is very useful, especially if you are a new database administrator. It walks you through all the steps necessary to successfully backup a database as shown in Figure 11-7.

Figure 7: The First Page of the SQL Server Backup Wizard

The second method available for you to create database backups is to use the BACKUP DATABASE statement in a Transact-SQL statement. Two parameters that must be present are the name of the database as well as the backup device that will be used. Optionally, you can also use the INIT or FORMAT parameters. INIT is used to overwrite the backup device and write the backup as the first file on the backup device. If there is no existing media header, then INIT automatically creates one. FORMAT is used when the media is used for the first time so that the backup device is completely initialized. INIT does not need to be specified if FORMAT is used.

Let’s take a look at an example of using Transact-SQL to perform a database backup. The following example backs up the entire Authors database to the Authors_1 backup device.

BACKUP DATABASE Authors
TO Authors_1
WITH FORMAT

The third method of creating database backups consists of using SQL Server Enterprise Manager. Exercise 11-1 shows you how to create a database backup using this method. Exercise 11-1 assumes that the Northwind database is installed on your SQL Server 7.0 system.

Exercise 11-1: Creating a Database Backup Using SQL Server Enterprise Manager

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a SQL Server and click the + to expand it.
  4. Select Databases and click the + to expand it.
  5. Right click on Northwind, highlight Task on the menu, and select Backup Database… from the sub-menu.
  6. Type Northwind backup in the Name: dialog box.
  7. In the Backup section choose the radio button to the left of Database – Complete.
  8. In the Destination section click the Add… button to create a new backup device.
  9. Append the name NW.BAK to the path listed in the dialog box of File name:.
  10. Click the OK button.
  11. In the Overwrite section, choose the radio button to the left of Append to Media. Figure 11-8 shows how your SQL Server Backup window should appear at this point.
  12. Click the OK button. A backup in progress window appears showing you the progress of the database backup.
  13. A window appears to notify you that the backup operation completed successfully. Click the OK button.

Figure 8: The General Tab of SQL Server Backup for the Northwind Database

Interrupted Backup Operations

If a backup operation is interrupted for any reason (such as a power loss on the SQL Server system), it is still possible to restart the backup operation from the point it was interrupted using Transact-SQL. To be successful, you must use the same clauses as they were used in the original BACKUP DATABASE statement. The additional parameter necessary to resume the interrupted backup operation is RESTART. Let’s look at an example of restarting an interrupted backup operation. If you originally used the Transact-SQL statement shown in Example 1 to start the backup, then you need to use the Transact-SQL statement shown in Example 2 to restart the interrupted operation.

Example 1:

BACKUP DATABASE Northwind
TO NWBAK

Example 2:

BACKUP DATABASE Northwind
TO NWBAK
WITH RESTART

Exam Watch: Restarting an interrupted backup can only be done with removable media, such as tapes. An interrupted backup cannot be restarted with disk devices.

Performing a Transaction Log Backup

The use of transaction log backups helps to increase the recoverability of a database. They are more complex to work with, however, than using only database backups. If anything happens to break the sequence of transaction log backups, you should create a new database backup and start backing up the transaction logs again at that point. The previous transaction log backups are not necessary. Therefore, they can be safely overwritten. There are several reasons that you may decide to perform transaction log backups. Two of these are:

When you create a transaction log backup, the starting point of the backup is where the previous transaction log backup ended. Another location may be where the most recent database or differential database backup ended if a transaction log backup has never been accomplished. After SQL Server 7.0 finishes backing up the transaction log, it truncates the inactive portion of the transaction log. The inactive portion is no longer used during the recovery process that the database goes through when SQL Server starts. This is because all of the transactions in that part of the log are complete. The active portion of the transaction log contains transactions that are still running and have not completed yet.

Exam Watch: Database and differential database backups contain the active portion of the transaction logs automatically.

There are three methods available to create a transaction log backup: the Create Backup Wizard, Transact-SQL, and SQL Server Enterprise Manager. The Create Backup Wizard is very useful, especially if you are a new database administrator. It walks you through all the steps necessary to successfully backup a database as you will see as you complete Exercise 11-2. Exercise 11-2 assumes that the Northwind database is installed on your SQL Server 7.0 system.

Exercise 11-2: Creating a Transaction Log Backup Using the SQL Server Backup Wizard

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a SQL Server and right click on it.
  4. Highlight Tools and select Wizards… from the sub-menu.
  5. In the Select Wizard window, click the + next to Management.
  6. Highlight Backup Wizard and click the OK button.
  7. Click the Next button.
  8. Select the Northwind database from the Databases: drop down box.
  9. Click the Next button.
  10. Type Northwind backup in the Name: dialog box.
  11. Click the Next button.
  12. Select the radio button located to the left of Transaction log. Backup the record of all the changes made to the database.
  13. Click the Next button.
  14. Select the radio button located to the left of Append to the backup media. Notice that the NW.BAK disk backup device is already shown since you created it in Exercise 11-1.
  15. Click the Next button.
  16. Click the Next button.

Click the Finish button. The transaction log of the Northwind database is backed up to NW.BAK.

The second method available for you to create transaction log backups is to use the BACKUP LOG statement in a Transact-SQL statement. Two parameters that must be present are the name of the database to which the transaction log backup belongs to as well as the backup device that will be used. Optionally, you can also use the INIT or FORMAT parameters. INIT is used to overwrite the backup device and write the backup as the first file on the backup device. If there is no existing media header then INIT automatically creates one. FORMAT is used when the media is used for the first time so that the backup device is completely initialized. INIT does not need to be specified if FORMAT is used.

Let’s take a look at a couple of examples of using Transact-SQL to perform transaction log backups. The following example creates a transaction log backup for the Authors database.

BACKUP LOG Authors
TO Authors_log1

The next example shows how to back up the undamaged active transaction log even when the Authors database is damaged and not accessible.

BACKUP LOG Authors
TO Authors_log1
WITH NO_TRUNCATE

The third method of creating transaction log backups consists of using SQL Server Enterprise Manager. The steps for creating this type of backup is the same as the steps you accomplished in Exercise 11-1. The exception is that you choose Transaction log in Step 7 instead of choosing Database Complete.

Performing a Differential Backup

We discussed earlier in the chapter that performing differential backups (in addition to your use of database backups) could increase the speed of your backup operations. Differential database backups do not allow a database to be restored to the exact point of failure like transaction log backups do. They only restore the database to the point in time that the differential database backup was created.

There are three methods available to create a differential backup; the Create Backup Wizard, SQL Server Enterprise Manager, and Transact-SQL. The Create Backup Wizard is very useful, especially if you are a new database administrator. It walks you through all the steps necessary to successfully backup a database as you saw when you completed Exercise 11-2. The only difference in using the Create Database Wizard for differential backups is that you select the radio button to the left of Differential database. This will backup only new and changed data as shown in Figure 11-9.

Figure 9: Selecting Differential Database Backup in the Create Backup Wizard

The second method of creating transaction log backups consists of using SQL Server Enterprise Manager. The steps for creating this type of backup is the same as the steps you accomplished in Exercise 11-1. The only exception is that you choose Database Differential in Step 7 instead of choosing Database Complete.

The third method available for you to create differential backups is to use the BACKUP DATABASE statement in a Transact-SQL statement using the DIFFERENTIAL clause. This is implemented so that only the parts of the database that have changed after the last database backup was created are backed up. Two other parameters that must be present are the name of the database backup as well as the backup device that will be used. Optionally, you can also use the INIT or FORMAT parameters. INIT is used to overwrite the backup device and write the backup as the first file on the backup device. If there is no existing media header, then INIT automatically creates one. FORMAT is used when the media is used for the first time so that the backup device is completely initialized. INIT does not need to be specified if FORMAT is used.

Let’s take a look at an example of using Transact-SQL to perform a differential backup. The following example creates a differential database backup. This backup is appended to the backup device that holds the Authors database backup. It was illustrated while discussing the use of Transact-SQL to backup the Authors database.

BACKUP DATABASE Authors
TO Authors_1
WITH DIFFERENTIAL

Performing a File or Filegroup Backup

Performing a file or filegroup backup copies one or more files of a designated database, thus allowing a database to be backed up in smaller units. It is important to remember that when you create file and filegroup backups, the transaction log is not backed up by the backup operation. You must make sure that all changes to the database file are available in case they are needed for a later restore operation. To do this, you must perform transaction log backups after a file or filegroup backup has been created.

There are two methods available to perform a file or filegroup backup: using SQL Server Enterprise Manager and Transact-SQL. If you decide to use SQL Server Enterprise Manager, then the steps for creating this type of backup are the same as the steps you accomplished in Exercise 11-1 with a couple of exceptions. In Step 7 you must choose the radio button located to the left of File and filegroup instead of choosing Database Complete. When you select this radio button, the dialog box to the right of it is no longer ghosted. Clicking on the ellipsis brings up the Specify Filegroups and Files window (shown in Figure 11-10) that allows you to select the file or filegroup that you want to backup for the database.

Figure 10: Selecting a Filegroup that will be a part of the Backup Operation

The second method available for you to create database backups is to use the BACKUP DATABASE statement in a Transact-SQL statement. Parameters that must be present are the name of the database as well as the backup device that will be used. You must also use the FILE clause or the FILEGROUP clause to specify each file or each filegroup, respectfully, on which to perform the backup operation.

Let’s take a look at an example of using Transact-SQL to perform a file and filegroup backup. The following example backs up the engineers_data_6 file, as well as the new_vendors filegroup of the SDC database to the SDC.BAK backup device.

BACKUP DATABASE SDC
FILE = 'engineers_data_6',
FILEGROUP = 'new_vendors',
TO SDC.BAK

Backing up the master, model, msdb, and distribution Databases

You perform backup operations on the master, model, msdb, and distribution databases as follows:

master Database

You perform backup operations on the master database using the same three methods that we have discussed for completing full backups on user databases. Master should be backed up regularly and frequently. If it is damaged, then SQL Server may not be able to start.

There are several operations that cause master to be updated. You should perform a backup of master after any of the following items occur:

Exam Watch: You can only create full database backups of master.

model, msdb and distribution Databases

You perform backup operations on the model, msdb, and distribution databases using the same three methods that we have discussed for completing backups on user databases. You can use any of the available types of backup operations for model, msdb and distribution. It is highly unlikely that model and msdb will become very large, so you may decide to backup those databases on a regular basis instead of having to deal with transaction log backups. Depending on your environment, distribution may not become very large either, so you may decide to also back it up on a regular basis rather than managing transaction log backups.

As with master, it is important to back up model, msdb, or distribution after any operation that updates the database. If model is damaged and there is no current backup available, then all user-specific template information is lost and needs to be re-created manually. If msdb is damaged, then all scheduling information used by the SQL Server Agent is lost and needs to be re-created manually by using SQL Server Enterprise Manager. If distribution is damaged, then any replication information used by the SQL Server replication utilities is lost and needs to be manually recreated.

Now that you have seen the different methods of backing up SQL Server 7.0, here is a quick reference for possible scenario questions with the appropriate answer:

"The employee database does not change very often…" Use a database backup on a weekly basis.
"The company depends on the financial database…" Use a combination of full database backups, differential backups, and transaction log backups to ensure that you have backups of the most current data. The transaction log backups need to be scheduled based upon the amount of time that you can afford to lose any data.
"The database has grown so big that there is not enough time to back it up completely during the night…" Perform file or filegroup backups on it nightly. For example you may need to split it over two or three nights to completely back up the entire database. You could also perform a differential backup if you already had a complete backup of the database. Another option is to perform a full backup on weekends and differentials nightly.
"You have added two databases to SQL Server …" Perform a full backup of the master database.
"Marissa is a new database administrator in your company and she is nervous about backing up databases…" Have her use the Create Database Wizard until she is comfortable with other methods of performing backup operations.

Using the Database Maintenance Plan Wizard

It can get very monotonous and repetitious having to perform database backups as well as other core maintenance tasks on system and user databases. The Database Maintenance Plan Wizard can be very useful in ensuring that your databases continue to perform well by performing regular back ups and checking for inconsistencies. The Database Maintenance Plan Wizard can schedule the following maintenance tasks to run automatically:

Figure 11: Creating a Database Maintenance Plan that Removes Backup Files Older than Four Weeks

A neat feature is that the results generated by the maintenance tasks can be written as a report to a text file, HTML file, or even e-mailed to an administrator! Exercise 11-3 gives you the opportunity to become familiar with all of the screens and several of the options available in the Database Maintenance Plan Wizard. The Database Maintenance Plan Wizard is started using the SQL Server Enterprise Manager.

Exercise 11-3: Using the Database Maintenance Plan Wizard

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a SQL Server and click the + to expand it.
  4. Right click on Database Maintenance Plans and select New Plan….
  5. Click the Next button.
  6. Type DB Maintenance Plan1 in the Plan name dialog box if the name is blank.
  7. Click the radio button located to the left of All system databases (master, model and msdb).
  8. Click the Next button.
  9. Place a check mark in the box located to the left of Reorganize data and index pages.
  10. Place a check mark in the box located to the left of Remove unused space from database files.
  11. Click the Next button.
  12. Place a check mark in the box located to the left of Perform internal data integrity tests.
  13. Place a check mark in the box located to the left of Attempt to repair any minor problems.
  14. Place a check mark in the box located to the left of Perform database integrity tests.
  15. Place a check mark in the box located to the left of Attempt to repair any minor problems.
  16. Place a check mark in the box located to the left of Perform these tests before backing up the database or transaction log.
  17. Click the Next button.
  18. Place a check mark in the box located to the left of Backup the database as part of the maintenance plan.
  19. Place a check mark in the box located to the left of Verify the integrity of the backup upon completion. We will discuss verification more in the next section of the chapter.
  20. Click the Change… button.
  21. Click the Change… button.
  22. Change Daily Frequency to 03:00 AM.
  23. Click the OK button.
  24. Click the OK button.
  25. Click the Next button.
  26. Place a check mark in the box located to the left of Backup the transaction log of the database as part of the maintenance plan.
  27. Place a check mark in the box located to the left of Verify the integrity of the backup upon completion.
  28. Click the Next button.
  29. Place a check mark in the box located to the left of Write report to a text file in directory:
  30. Add MaintPlan1.txt to the path in the dialog box located to the right of the box you placed the check mark in.
  31. Click the Next button.
  32. Click the Next button.
  33. Click the Finish button.

You just successfully created a maintenance plan for all of the system databases. You may decide that you need to create additional maintenance plans as shown in Figure 11-12.

fig 11-12.gif (16613 bytes)

Figure 12: Multiple Database Maintenance Plans for the P233 SQL Server

Checking Database Integrity

I have had the unfortunate opportunity of having to restore a database backup that someone else had created. It did not go well, as files on the tape were corrupt. After talking to the individual that had created the backup tape I verified my suspicion that they had not performed a verification of the backup when it was created. We were finally able to recover from the bad tape. Despite the recovery, lots of information was manually recreated to make up for it.

Verifying a backup simply means that the backup is checked to ensure that all files have been written and that the files in the backup are readable. Verifying backups does not occur automatically, and you do not have to use them if you don’t wish. You have already seen one location in which verification can be selected; in the Database Maintenance Plan that you created in Exercise 11-3. Another location to select verification is from the Options tab when you backup a database from SQL Server Enterprise Manager as shown in Figure 11-13.

Figure 13: The Options Tab displaying that Verification after Completion of Backup will be accomplished

Obviously it is wise to verify your backup as it can save your database if you ever need to restore it. My coworker has not failed to verify any backup he has created since the "incident". Verifying a backup, however, does not check that the structure of the data contained within the backup is correct, only that the files are readable as previously stated. To verify the structure of the data in your database prior to creating a backup, you should perform a database consistency check. Database consistency checks can be accomplished using the Database Maintenance Plan Wizard and also by using DBCC CHECKDB.

Certification Summary

Several factors can affect the integrity of your databases such as media failure, user errors, and server loss. You need to have a solid backup and restore strategy in place to make sure that you do not lose any vital information that your company depends on. You may decide that you need to use transaction logs or implement a standby server.

Your backup plan must be centered on items that affect your company, such as whether or not your location susceptible to any particular natural disasters. After you create the backup plan, you need to thoroughly test it out to make sure that it will work when you need it. It is much better to find out that it doesn’t work while testing than during the real thing!

Implementing backups in SQL Server is very flexible. Two of the items you can use to perform backups is disk drive media or tape drive media. The type of backup you perform should be very flexible, and you need to select the best one for your situation.

The Database Maintenance Plan Wizard can be a big help to you with performing database backups and other core maintenance tasks. You can setup several different maintenance plans if you need to encompass managing several different database actions.

Performing a verification of the backups you create can prevent an embarrassing situation if you ever need to complete a restore operation from the backup. Verification only makes sure that the files are readable and does not check the integrity of the data in the files.

Two-Minute Drill