Standby server

5

5.1 Considerations for restore operations

Before you restore a database, you must consider several factors for a successful restore operation. For each specific restore scenario, a different level of

complexity is required for the restore operation. Data Protection for SQL Server provides various options for restoring a simple database from a full backup on the same server to restoring all databases on a different server - in a disaster recovery scenario, for example.

It is important to understand that the restore capabilities for a Microsoft SQL Server are directly related to the backup operations performed for the databases.

A well-planned and implemented set of backup routines are crucial for a successful restore operation; in other words, you cannot restore what has not been backed up. See 2.3, “Microsoft SQL Server backup planning” on page 42 for a discussion of Microsoft SQL Server backup planning.

Another important point is that you may experience unexpected hardware or software failures during a restore operation. You can minimize this risk by regularly testing your restore routines to make sure they are working as expected.

When you restore a database, keep in mind that the data that exists in the database is overwritten and is no longer available after the restore is complete.

The Data Protection for SQL Server Restore Databases feature enables you to restore databases or parts of databases only from full, differential, and log backups. Although only VSS full backups are supported, Legacy differential and Legacy log backups can be applied after a full VSS backup has been restored.

A master database restore requires special attention. See 5.4.3, “Restoring Microsoft SQL Server system databases” on page 190 for step-by-step instructions.

5.1.1 Displaying list of backups

Before you start a restore process, you must check which backups are available for the database you want to restore.

Using the Data Protection for SQL Server GUI, you can use the Restore Databases tab to display full, differential, and log backups, and the Restore Groups/Files tab to display full, log, group, set, and file backups. Legacy and VSS backups are displayed together. For more information, refer to 4.4.1,

“Displaying existing backups using the GUI” on page 156.

Chapter 5. Data Protection for SQL Server restore 181 From the Data Protection for SQL Server CLI, you can issue the query command.

For more information about the query command, see 4.8.1, “Displaying existing backups using the CLI” on page 175. or refer to IBM Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server Installation and User’s Guide, SC32-9059.

5.1.2 VSS considerations

Be aware of the following considerations when performing VSS restores. Unless otherwise specified, “VSS restores” refers to all restore types that use VSS (VSS Restore, VSS Fast Restore, and VSS Instant Restore):

򐂰 If you plan to perform a VSS Restore of the master database, see 5.4.3,

“Restoring Microsoft SQL Server system databases” on page 190.

򐂰 A VSS Instant Restore overwrites the entire contents of the source volumes.

However, you can avoid overwriting the source volumes by selecting the Disable VSS Instant Restore option. This option bypasses volume-level copy and uses file-level copy instead to restore the files from a VSS backup that resides on local shadow volumes. We recommend that the source volume contain only the SQL Server database.

򐂰 Be aware that when a VSS restore from local shadow volumes is performed, the bytes transferred will display “0”, which occurs because no data (“0”) is restored from the Tivoli Storage Manager server.

򐂰 To perform a VSS Instant Restore, the IBM Tivoli Storage Manager for Copy Services Hardware Devices Snapshot Integration Module must be installed.

򐂰 When performing VSS Instant Restores, you must make sure that any previous background copies (that involve the volumes being restored) are completed prior to initiating the VSS Instant Restore.

5.2 Performing restores using the GUI

Using the Data Protection for SQL Server GUI, you can perform full and partial restores of your databases. In a full restore, you restore all files belonging to the database, while in a partial restore, you choose to restore only individual files from the database.

If you plan to fully restore your database using full, log, and differential backups, click the Restore Database tab in the Data Protection for SQL Server GUI. If you plan to restore only parts of your database using full, file, group, set, or log backups select the Restore Groups/Files tabs in the Data Protection for SQL Server GUI.

In next sections we discuss how to restore Microsoft SQL Server databases using the Data Protection for SQL Server GUI.

5.2.1 Performing a full database restore

Complete the following steps to restore a Microsoft SQL Server database using full, differential, and log backups.

1. Start the Data Protection for SQL Server GUI.

2. Click the Restore Database tab.

3. Check Show Active and Inactive if you want to display inactive backup objects in addition to active backup objects.

4. Click the plus sign (+) in the tree view to the left of the SQL server that you want to work with. All Microsoft SQL Servers backed up under the Tivoli Storage Manager node name are displayed.

5. Click the plus sign in the tree view to show the names of databases backed up on the Tivoli Storage Manager server. All databases backed up under the Tivoli Storage Manager node name are displayed, even those databases that were already dropped from the SQL server but were not expired from the Tivoli Storage Manager server inventory.

6. Click the plus sign in the tree view to the left of the SQL server that you want to restore from. The tree expands again and shows the databases available for restore processing.

7. Click the plus sign in the tree view to the left of the database that you want to restore. The database expands to show the types of backups available for restore.

8. Click the selection box in the tree view to the left of the full, differential, or transaction log backup that you want to restore.

9. Select the desired restore options for your backup.

10. Click Restore. The Restore Progress dialog appears.

11. Click OK. The restore is complete.

Note: You can use both the Restore Database and Restore Groups/Files tabs to fully restore your database from a full backup and to apply the

subsequent transaction log files. The difference between these two tabs is that in the Restore Groups/Files tab, you can choose individual files from the database full backup to be restored, while in the Restore Database tab, you restore all files from your database.