Backing Up and Restoring the Database

[Previous] [Next]

We began our discussion of backing up and restoring the database in Chapter 17, when we explored some of the built-in database maintenance routines configurable through the SMS Administrator Console. In this section, we'll review the procedure for backing up and restoring the database directly through SQL Server.

The contents of the database and transaction log can be backed up to a device such as a tape drive or to another file location on the server. The frequency of the backup is up to you, the SMS administrator. Generally, you will back up the SMS database as frequently as necessary to ensure a current and accurate restoration of the data. A common database strategy involves performing a complete backup of the database once a week, with incremental backups of the data that has changed each day between full backups.

TIP

---

As you've seen throughout this book, SMS components have frequent communication with the SMS database. Before implementing a production site, develop and test a backup and restore strategy that will adequately protect your data.

Remember that you should turn off the Truncate Log On Checkpoint option before you back up the transaction log. This option is enabled by default and causes the transaction log to contain only few, if any, recent transactions. For most of the time, the default Truncate Log On Checkpoint option setting will be appropriate, and you will need to turn off this option only when backing up the SMS database.

NOTE

---

Several third-party backup programs, such as Seagate's Backup Exec 7.2 for Windows NT, include modules designed specifically for backing up SQL Server databases. If you have access to such a product, you can have it perform the backup as part of its systemwide backup routine, eliminating the need to configure a backup redundantly through SQL Server or through the SMS Administrator Console.

To preserve the integrity of the data, it is important that no SMS components try to access the SMS database when the backup or restore is taking place. Be sure that no SMS Administrator Consoles are running and that all SMS components on the site server have been stopped. In addition, when you are restoring the database be sure to set the database to single-user mode. This is set as a property of the database. Note that you will not be able to set the single-user mode option if any open connections exist to the database.

Backing Up and Restoring Using SQL Server 6.5

Let's begin with backing up and restoring the database using SQL Server 6.5. Recall from Chapter 17 that to back up the database, you must identify a backup device. You can do this ahead of time, or you can create one as you configure the backup.

To back up the database, follow these steps:

1. In Enterprise Manager, navigate to the Databases folder and expand it.
2. Select your SMS site database, right-click on it, and choose Backup/Restore from the context menu to display the Database Backup/Restore Properties window, shown in Figure 18-16.



Figure 18-16. The Database Backup/Restore Properties window.

3. On the Backup tab, verify that your SMS database has been selected, and that the Entire Database option is enabled.
4. If you want to add this backup to existing backups on the backup device, enable the Initialize Device option in the Options section. If you want to overwrite what is already on the device, do not enable this option. If you do enable the Initialize Device option, you can also define an expiration interval for the backup.
5. If you have already created a backup device, select it from the Backup Devices list. To identify a new backup device, click New to display the New Backup Device dialog box, shown in Figure 18-17.



Figure 18-17. The New Backup Device dialog box.

6. Enter a name for the device, confirm or define a location, and specify whether the backup device is a disk or a tape. Click Create to create the device and return to the Backup tab of the Database Backup/Restore Properties window. The new backup device will now be shown in the window.
7. With the backup device selected, click Backup Now to begin the backup process. If you enabled the Initialize Device option, a dialog box will appear allowing you to confirm or modify a volume label for the backup. If previous backups exist for the device, you will be prompted to confirm their deletion. Then click OK.
8. When the backup is complete, a message to that effect will be displayed. Click OK.

The restore process is essentially the reverse of the backup process. To restore the database, follow these steps:

1. In Enterprise Manager, navigate to the Databases folder and expand it.
2. Select your SMS database, right-click on it, and choose Edit from the context menu to display the Edit Database Properties window.
3. Click on the Options tab, shown in Figure 18-18. Check the Single User check box, and then click OK to return to Enterprise Manager.



Figure 18-18. The Options tab of the Edit Database Properties window.

4. Right-click on the SMS database, and choose Backup/Restore to display the Database Backup/Restore Properties window.
5. Click on the Restore tab, shown in Figure 18-19. Verify that your SMS database has been selected and that the Database And/Or Transaction Logs option is enabled.



Figure 18-19. The Restore tab of the Database Backup/Restore Properties window.

6. Select the backup you want to restore from the Backup History list, and then click the Info button to display the Backup History Information dialog box, shown in Figure 18-20.



Figure 18-20. The Backup History Information dialog box.

7. Verify the backup device and volume label, and click Close to return to the Restore tab.
8. Click Restore Now to begin the restoration process.
9. When the restore process has completed successfully, SQL Server will display a message to that effect. Click OK.

NOTE

---

This process represents the basic configuration procedure for backing up and restoring the SMS database; it should in no way be considered exhaustive of every backup/restore option available. As mentioned, you should consider adding SQL Server to your ongoing professional training goals if you will be managing the SMS database extensively through SQL Server.

Backing Up and Restoring Using SQL Server 7.0

The backup and restore process for SQL Server 7.0 is similar to that for SQL Server 6.5. As with SQL Server 6.5, you can either create a backup device ahead of time through Enterprise Manager or while you configure the backup.

To back up the SMS database using SQL Server 7.0, follow these steps:

1. In Enterprise Manager, navigate to the Databases folder and expand it.
2. Select the SMS database you want to back up, right-click on it, and choose Properties to display the Database Properties window.
3. Click on the Options tab, shown in Figure 18-21. Select the Single User check box, and click OK to return to Enterprise Manager.



Figure 18-21. The Options tab of the Database Properties window.

4. Right-click on the database entry again, choose All Tasks from the context menu, and then choose Backup Database to display the SQL Server Backup Properties window, shown in Figure 18-22.



Figure 18-22. The SQL Server Backup Properties window.

5. On the General tab, confirm that your SMS database is selected. Modify the name of the backup if you want, and verify that the Database - Complete option has been selected.

NOTE

---

Unlike a backup using SQL Server 6.5, here you do have the option of also performing a differential backup.

6. To specify a backup device, click the Add button to display the Choose Backup Destination dialog box, shown in Figure 18-23. Enter a filename and select an existing backup device from the Backup Devices list or select New Backup Device to create a new device.



Figure 18-23. The Choose Backup Destination dialog box.

7. If you select New Backup Device, the Backup Device Properties - New Device window will appear, as shown in Figure 18-24. Enter a name for the device, and then click OK to return to the Choose Backup Destination dialog box.



Figure 18-24. The Backup Device Properties - New Device window.

8. Click OK again to accept the device destination and return to the SQL Server Backup Properties window.
9. Remove any other backup devices that may be listed in the Backup To list on the General tab, such as a temp file. Set whatever other options you want on the General and Options tabs, and then click OK to begin the backup process. (You can click the Help button for more information about each of the options available on these tabs.)
10. When the backup is complete, a message to that effect will be displayed. Click OK.

To restore the database, follow these steps:

1. In Enterprise Manager, navigate to the Databases folder and expand it.
2. Select your SMS site database, right-click on it, choose All Tasks from the context menu, and then choose Restore Database to display the Restore Database Properties window, shown in Figure 18-25.
3. On the General tab, verify that the correct database is selected and that the Database option in the Restore section has been enabled.
4. In the Parameters section, select the appropriate backup device from the Show Backups Of Database drop-down list. The First Backup To Restore drop-down lists displays in chronological order the database backups for this device. This option allows you to restore selective differential backups if you performed them. Select the appropriate entry.



Figure 18-25. The Restore Database Properties window.

5. Click OK to begin the restore process.
6. When the restore process has completed successfully, SQL Server will display a message to that effect. Click OK.

In this section, we've looked at the procedures for backing up and restoring SMS databases using SQL Server. Note that what we discussed are only essential procedures. You should consult the SQL Server documentation for other configuration options.