Microsoft SQL Server 7.0 System Administration Training Kit

This lesson explains how to restore backups of various types. Before undertaking any type of restore operation, you should make sure that you have a valid backup set and that you have all devices that contain the backup set.

After this lesson, you will be able to

• Restore backups from different backup types

Estimated lesson time: 90minutes

Restoring from a Complete Database Backup

When you restore a database from a complete database backup, SQL Server re-creates the database and all of its associated files and then places them in their original locations. All database objects are re-created automatically. You do not need to rebuild the database schema before you restore the database.

You will typically restore from a complete database backup when

• The physical disk containing the database is damaged
• The entire database is damaged, corrupted, or deleted
• An identical copy of the database is being restored to a different SQL Server, such as a standby SQL Server

Specifying a Recovery Option

The RECOVERY option initiates the recovery process so that your database is returned to a consistent state. Use the following guidelines in choosing the RECOVERY or NORECOVERY option:

• If you use a complete database backup strategy and do not have any transaction log or differential backups, specify the RECOVERY option.
• If any transaction log or differential backups exist, specify the NORECOVERY option to postpone the recovery process until the last backup is restored.

The following example assumes that a complete backup exists on the nwindbac named backup device and that two backup sets are appended to that device. The Northwind database is completely replaced by the second backup set on the nwindbac named backup device. Finally, the recovery process returns the database to a consistent state (rolls forward committed changes and rolls back uncommitted activities).

Exercise: Creating the nwcopy Database

In this exercise, you will restore the nwcopy database from a backup provided on the Supplemental Course Materials CD-ROM. You will use this database in the other exercises in this chapter to practice restoring databases.

• To create the nwcopy database

1. Log on to your computer as Administrator or as another user that is a member of the local Administrators group
2. Copy the C:\SQLAdmin\Exercise\Ch10\Nwc1.bak file to C:\Mssql7\Backup on your local hard disk.
3. Open SQL Server Query Analyzer and log on to the (local) server with Microsoft Windows NT authentication. Your account is a member of the Windows NT Administrators group, which is automatically mapped to the SQL Server sysadmin role.
4. Open and execute the C:\SQLAdmin\Exercise\Ch10\Setupnwc.sql script installed on your hard disk drive from the Supplemental Course Materials CD-ROM.

This script restores the nwcopy database, which is used in the other exercises in this chapter.

Exercise: Modifying the nwcopy Database

In this exercise, you will execute a script that adds a row to the Products table. You will then write and execute a query that returns the new row.

• To modify the nwcopy database

1. Open a query window, open C:\Sqladmin\Exercise\Ch10\Addprod.sql, review its contents, and then execute it.

This script adds the new product Maple Flavor Pancake Mix to the Products table.

2. Review the results to confirm that the new row was added.

Exercise: Backing Up the nwcopy Database

In this exercise, you will execute a script that backs up the nwcopy database to a single backup device.

• To back up the nwcopy database

Open C:\Sqladmin\Exercise\Ch10\Makeback.sql, review its contents, and then execute it.

This script backs up the nwcopy database to a single backup device. This backup device has a logical name of nwc2 and a physical name of C:\Mssql7 \Backup\Nwc2.bak.

Exercise: Simulating Database Damage

In this exercise, you will execute a script that damages the database by updating all rows in the Products table. You will then write and execute a query to confirm that, due to the erroneous update, the product Maple Flavor Pancake Mix no longer appears in the Products table.

• To simulate accidental data modification

1. Open a query window, open C:\Sqladmin\Exercise\Ch10\Dataloss.sql, review its contents, and then execute it.

This script damages the database by updating all rows in the Products table.

2. Review the result to confirm that the product Maple Flavor Pancake Mix can no longer be found in the Products table, as its name has been incorrectly changed.
3. Close the query window.

Exercise: Using SQL Server Enterprise Manager to Restore the nwcopy Database

In this exercise, you will use SQL Server Enterprise Manager to restrict access to the nwcopy database, restore from a complete database backup, and then allow access to the database after the restore process is complete.

IMPORTANT

• To restore the nwcopy database from a full database backup

1. Open SQL Server Enterprise Manager.
2. In the console tree, expand the Databases folder.
3. In the console tree, right-click the nwcopy database icon, and then click Properties.
4. On the Options tab, check the Single User and DBO Use Only options to restrict access to the database during the restore process.
5. Click OK to close the dialog box and save your changes to the database options.
6. In the console tree, right-click the nwcopy database icon, point to All Tasks, and then click Restore Database.
7. In the Restore Database dialog box, review the automatic selections:

• On the General tab, Database is selected for Restore. This allows a complete database backup and any associated differential or transaction log backups to be restored.
• The First Backup To Restore option is set to the last complete backup of the nwcopy database. You can change this selection to restore from older backups when necessary. The backup history listed here reflects entries in the msdb database. If the backup set has since been overwritten, you will not be able to restore it.
• In the list of backup sets, the backup set you made previously, called nwcopy-Complete, with a filename of C:\Mssql7\Backup\Nwc2.bak, is checked.

8. On the Options tab, check Leave Database Operational. No Additional Transaction Logs Can Be Restored, as you have no transaction log backups to restore. This corresponds to the RECOVERY option of the RESTORE statement.
9. Click OK to perform the restore.

Exercise: Confirming Data Recovery

In this exercise, you will write and execute a query that returns the Maple Flavor Pancake Mix product and another that lists all of the products in the Products table.

• To confirm that data was recovered

Open a query window, open C:\Sqladmin\Exercise\Ch10\ChkRest.sql, review its contents, and then execute it.

Restoring from a Differential Backup

When you restore a database from a differential database backup, the following occur:

• Only the parts of the database that have changed since the last complete database backup are restored.
• The database is returned to the exact condition that it was in when the differential backup was performed.

The restore often takes less time than it does to apply a series of transaction logs representing the same database activity.

When you restore from a differential backup, consider the following facts and guidelines:

• The syntax for restoring a differential backup is the same as for restoring a complete database backup. The backup device specified with the FROM clause and the file number specified with the FILE option must simply refer to a differential backup rather than a complete backup.
• You must restore a complete database backup, specifying the NORECOVERY option, before you can restore a differential backup.
• Specify the NORECOVERY option when restoring a differential database backup if there are transaction logs to be restored; otherwise, specify the RECOVERY option.

The following example restores a differential backup without recovering the database. The nwindbacdiff device contains a differential backup. Specifying the NORECOVERY option allows you to restore transaction logs. The RECOVERY option will be specified for the last transaction log restore.

Restoring from a Transaction Log Backup

When you restore from a transaction log backup, SQL Server reapplies changes to the database that are recorded in the transaction log.

You will typically restore transaction logs as a means of applying changes made to the database since the last complete database or differential backup. In addition, you can restore transaction logs to recover a database up to a specific point in time.

Considerations for Restoring Transaction Logs

Although restoring a differential backup may speed up the restore process, you may have to restore additional transaction log backups that were created after a differential backup, to ensure data consistency.

Before you restore any transaction logs, you first must restore the complete database backup, specifying the NORECOVERY option. When you have multiple transaction logs to apply, specify the NORECOVERY option for all transaction logs except the last one. This causes SQL Server to suspend the recovery process until the last transaction log is restored.

The syntax for the RESTORE LOGstatement is as follows:

NOTE

The following example assumes that a complete database backup exists on the nwindbac named backup device and that two transaction log backups exist on the nwindbaclog named backup device. Three separate restore operations are performed to ensure database consistency.

1. The first step restores from a complete database backup without recovering the database.

USE master RESTORE DATABASE northwind FROM nwindbac WITH NORECOVERY

2. The second step restores the first transaction log without recovering the database. The progress of the restore process is displayed.

USE master RESTORE LOG northwind FROM nwindbaclog WITH FILE = 1, STATS, NORECOVERY

3. The third step restores the second transaction log. The RECOVERY option returns the Northwind database to a consistent state, rolling forward any committed transactions and rolling back any uncommitted transactions:

USE master RESTORE LOG northwind FROM nwindbaclog WITH FILE = 2, RECOVERY

Restoring to a Specific Point in Time

When you restore transaction logs, you can restore to a specific point in time by using the STOPAT option. The following are some guidelines for using this option:

• Use the STOPAT option to recover a database to the state it was in at the exact moment before data corruption or some other event occurred.

For example, if you know that a malicious update to a database occurred at 11:00 a.m., you can restore the changes in the transaction log through 10:59 a.m. and not apply any changes that occurred after that point.

• You must specify the date and time at which to stop loading a backup onto the database. SQL Server restores all of the transaction log records that were written to the database before the specified point in time.

NOTE

The following example assumes that a complete database backup was made to the nwindbac named backup device at 8:00 p.m. on January 2, 1998. In addition, two transaction log backups were made at 10:00 a.m. and 1:00 p.m. on January 3, 1998, to the nwindbaclog named backup device. Only changes that occurred before 11:00 a.m. on January 3, 1998, must be restored. Three separate restore operations are performed to ensure database consistency:

1. The first step restores a database from a complete database backup without recovering the database.

USE master RESTORE DATABASE northwind FROM nwindbac WITH NORECOVERY

2. The second step restores the first transaction log without recovering the database.

USE master RESTORE LOG northwind FROM nwindbaclog WITH FILE = 1, NORECOVERY

3. The third step restores the second transaction log, applies changes that occurred before 11:00 a.m. on January 3, 1998, and recovers the database.

USE master RESTORE LOG northwind FROM nwindbaclog WITH FILE = 2, RECOVERY, STOPAT = 'January 3, 1998 11:00 AM'

Restoring from a File or Filegroup Backup

You can restore from a file or filegroup backup to reduce the time required to restore part of a very large database. Restore from a file or filegroup when a particular file was accidentally deleted or damaged.

When you restore from a file or filegroup, consider the following:

• SQL Server requires you to restore the filegroup backups as a unit if a table and its associated indexes exist on two different filegroups.
• SQL Server allows you to restore an individual database file from a complete database backup or from an individual file backup.
• You must apply all transaction logs that were created since the backup from which the file was restored, in order to bring the restored file or filegroup into a state that is consistent with the rest of the database. For this reason, the RECOVERY option is not allowed when restoring a file or filegroup. If you have not done a transaction log backup since making the backup from which you are attempting to restore a file or filegroup, the file or filegroup restore operation will terminate with an error. If the primary data file and the transaction log file are intact, you can make a transaction log backup, using the NO_TRUNCATE option, before restoring the file or filegroup. SQL Server applies only those transactions that affect the restored file.

The syntax for the RESTORE DATABASE statement is as follows:

The following example assumes that a database exists in three files: Nwind1, Nwind2, and Nwind3. The Nwind2 database file contains a single table and its related indexes. The Nwind2 database file was backed up onto the Nwind2bac backup device. One transaction log backup was performed after the Nwind2 file was last backed up. Nwind2 must be restored because the physical medium is damaged. The restore consists of two steps to ensure database consistency:

1. The first step restores the backup of the Nwind2 database file without rolling forward any committed transactions or rolling back any uncommitted transactions.

USE master RESTORE DATABASE northwind FILE = Nwind2 FROM Nwind2bac WITH NORECOVERY

2. The second step restores the transaction log backup. The RECOVERY option returns the Northwind database to a consistent state, rolling forward any committed transactions and rolling back any uncommitted transactions:

USE master RESTORE LOG northwind FROM nwindbaclog WITH RECOVERY

A Practical Example

In the following series of exercises, you will first perform a number of data modifications and backups of the nwcopy database. Then you will simulate a media failure and restore the database from your backups.

Exercise 1: Backing Up the nwcopy Database

In this exercise, you will execute a script that makes a complete database backup of the nwcopy database to the nwc3 named backup device. This backup is the baseline for the restore operation later.

• To perform a full database backup of the nwcopy database

Open a query window, open C:\Sqladmin\Exercise\Ch10\Compback.sql, review its contents, and then execute it. This script backs up the nwcopy database to the nwc3 named backup device.

Exercise 2: Modifying the nwcopy Database and Backing Up the Transaction Log

In this exercise, you will execute a script that adds a customer to the Customers table and confirms that the customer was added. Then you will execute another script that backs up the transaction log to the nwchange named backup device.

• To modify the nwcopy database and back up the transaction log

1. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Addcust1.sql, review its contents, and then execute it.

This script adds the Health Food Store as a customer to the Customers table and queries the table to return the new customer.

2. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Logback1.sql, review its contents, and then execute it.

This script backs up the transaction log of the nwcopy database to the nwchange named backup device.

Exercise 3: Modifying the nwcopy Database and Performing a Differential Backup

In this exercise, you will execute a script that adds another customer to the Customers table and returns that customer to confirm that the customer was added. You will then execute another script that performs a differential backup and appends it to the nwchange named backup device.

• To modify the nwcopy database and perform a differential backup

1. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Addcust2.sql, review its contents, and then execute it.

This script adds the Volcano Coffee Company to the Customers table and queries the table to return the new customer.

2. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Diffback.sql, review its contents, and then execute it.

This script performs a differential backup to capture all changes since the last complete database backup. The differential backup is appended to the nwchange named backup device.

Exercise 4: Modifying the nwcopy Database

In this exercise, you will execute a script that adds a third customer to the nwcopy database and confirms that the customer was added.

• To modify the nwcopy database

1. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Addcust3.sql, review its contents, and then execute it.

This script adds The Wine Cellar as a customer to the Customers table and queries the table to return the new customer.

NOTE

Exercise 5: Simulating Database Damage

In this exercise, you will simulate damage to the medium that stores the nwcopy database.

• To simulate damage to the database

1. Switch to SQL Server Enterprise Manager, and then exit.
2. Open SQL Server Service Manager, and then stop the SQL Server service.
3. Use Windows NT Explorer to rename the secondary data file for the nwcopy database from C:\Mssql7\Data\Nwcopy_data2.ndf to Nwcopy_data2.bad. Be careful not to change the primary data file, C:\Mssql7\Data\Nwcopy_data.mdf, or the transaction log file, C:\Mssql7\Data\Nwcopy_log.ldf.
4. Restart the SQL Server service.
5. Open SQL Server Enterprise Manager.
6. In the console tree, expand the Databases folder, and then click the nwcopy database icon.
7. If the Details pane shows database object icons, right-click the nwcopy database icon, point to View, and then click Taskpad.
8. SQL Server displays an error message stating that an error occurred while trying to access the database information.
9. Open Windows NT Event Viewer and examine the contents of the Application Log.

You should find an information message stating that there was a device activation error for the C:\Mssql7\Data\Nwcopy_data2.ndf file.

What should you do to restore and recover the nwcopy database?

Answer

Exercise 6: Performing a Transaction Log Backup of the nwcopy Database

In this exercise, you will execute a script that performs a transaction log backup after the simulated failure of the nwcopy database. The backup is appended to the nwchange named backup device.

NOTE

• To perform a transaction log backup after secondary data file failure

1. Open a query window.
2. Open C:\Sqladmin\Exercise\Ch10\Logback2.sql, review its contents, and then execute it.

This script uses the NO_TRUNCATE option to back up the transaction log of the nwcopy database when the database is not available.

Exercise 7: Examining the nwcopy Backups

In this exercise, you will use SQL Server Enterprise Manager to examine the contents and creation date of all nwcopy database backups.

• To examine available backups

1. Switch to SQL Server Enterprise Manager
2. In the console tree, expand the Management folder, and then click Backup.
3. In the details pane, right-click the nwc3 device, and then click Properties.
4. Click View Contents to examine the contents of the nwc3 device. Notice the type, description, and date and time of the backup set on the device.

What does the nwc3 device contain?

Answer

5. Click Close to close the Backup Media Contents dialog box. Click Cancel to close the Backup Device Properties dialog box.
6. Repeat steps 3, 4, and 5 to examine the contents of the nwchange device. Notice the type, description, and date and time of each backup set on the device.

What does the nwchange device contain?

Answer

Exercise 8: Reviewing the Restore Strategy

In this exercise, you will review the restore strategy suggested by SQL Server Enterprise Manager and determine whether it is appropriate.

• To review the suggested restore strategy

1. In the console tree, right-click the nwcopy database icon, point to All Tasks, and then click Restore Database.
2. The Restore Database dialog box appears. Verify that the nwcopy database is selected in the Restore As Database list.

Notice that four backup sets are listed. SQL Server automatically selects the most recent complete database backup and the corresponding differential and/or transaction log backup sets that should be restored to return the database to a consistent state. Three out of four backups are selected (full database, differential, and one transaction log).

Do you agree that the selected backups should be restored?

Answer

Why is the first transaction log backup not selected?

Answer

3. Click Cancel to close the Restore Database dialog box.

In Exercises 9, 10, and 11, instead of simply restoring the full database as suggested by SQL Server Enterprise Manager, you will restore the different backup sets individually. This is done so that you can see and understand the effect of each restore.

Exercise 9: Restoring the Complete and Differential Backups

In this exercise, you will use SQL Server Enterprise Manager to restore the complete database and differential backups and allow access to the database after the restore process is complete.

• To restore the complete and differential database backups

1. Open SQL Server Enterprise Manager.
2. In the console tree, expand the Databases folder.
3. In the console tree, right-click the nwcopy database icon, point to All Tasks, and then click Restore Database.
4. In the Restore Database dialog box, review the automatic selections:

• On the General tab, Database is selected for Restore. This allows a complete database backup and any associated differential or transaction log backups to be restored.
• In the Restore list, you should see the four backups you made. The first (complete), third (differential), and fourth (transaction log) backups are checked. To restore only the complete and differential database backup sets, uncheck the third checked backup set (nwcopy-Log2) to deselect it.

5. On the Options tab, click Leave Database Read-Only And Able To Restore Additional Transaction Logs, as you will restore the transaction log backups later. This corresponds to the RESTORE statement STANDBY option.
6. Check both Prompt Before Restoring Each Backup and Force Restore Over Existing Database.
7. Click OK to restore the complete database backup.
8. Click OK to restore the differential database backup.

Exercise 10: Examining the Contents of the Database

In this exercise you will execute a script that lists the new customers in the Customers table in order to evaluate the restore process.

• To examine the contents of the database

1. Open a query window, open C:\Sqladmin\Exercise\Ch10\Listcust.sql, review its contents, and then execute it.

This script determines whether the three new customers that were previously added to the Customers table were recovered.

Have all three new customers been recovered?

Answer

2. Close the query window.

IMPORTANT

Exercise 11: Restoring the Transaction Log Backup

In this exercise, you will use SQL Server Enterprise Manager to restore the transaction log and then allow access to the database after the restore process is complete.

• To restore the transaction log backup

1. Switch to SQL Server Enterprise Manager.
2. Expand the Databases folder.
3. In the console tree, right-click the nwcopy database icon, point to All Tasks, and then click Restore Database.
4. In the Restore Database dialog box, on the General tab, click the From Device option in the Restore section.
5. Click the Select Devices button.
6. In the Choose Restore Devices dialog box, click Disk for the Restore From option if it is not selected.
7. Click Add to add the backup device to the Restore From list.
8. In the Choose Restore Destination dialog box, click Backup Device.
9. In the Backup Device drop-down list, select nwchange. Click OK to accept the nwchange device.
10. If there are any devices other than nwchange in the Restore From list, highlight them and click Remove to remove them.
11. Click OK to accept nwchange as the device from which to restore.
12. Click the View Contents button to select a backup set from the nwchange device.
13. Check the check box next to the nwcopy-Log2 backup set. This is the transaction log backup taken after the simulated database failure. Click OK to accept the selected backup set. Note that the Backup number is now set to 3; this corresponds to selecting the third backup set with the FILE option of the RESTORE statement.
14. Click the Restore Backup Set option and the Transaction Log option if they are not selected.
15. On the Options tab, click Leave Database Operational. No Additional Transaction Logs Can Be Restored, as you have no more transaction log backups to restore. This corresponds to the RECOVERY option of the RESTORE statement.
16. Click OK to perform the restore.
17. Switch to SQL Server Query Analyzer. Open a query window, open C:\SQLAdmin\Exercise\Ch10\Listcust.sql, and execute it. All three new customer records are now present, indicating a successful restore of transactions backed up after a simulated database failure.

Lesson Summary

When restoring databases, you should obtain information about the backups that you plan to restore. Make sure that the files are valid and contain all of the backups that are required to restore the database to a consistent state. Use the NORECOVERY option if you have additional backups that must be restored. Use the RECOVERY option on the last backup to return the database to a consistent state.