Restoring an SQL database to an alternate machine

Answer

The following steps can be use to restore a database to an alternate location.

1. Copy the "dsm.opt" file from the originating (source) machine where the backup was taken to this alternate (target) restore machine (the default directory is c:\program files\tivoli\tsm\tdpsql).
2. If passwordaccess is set to generate in this "dsm.opt" file, it may be necessary to reset the password on the Tivoli Storage Manager Server for this node.
3. Launch the Data Protection for SQL GUI on the alternate (target) restore machine.
4. Click on the Restore Databases tab.
5. If this is the first time this node has attempted to contact the Tivoli Storage Manager Server from this machine, it will prompt for the Tivoli Storage Manager password.
6. Make sure that both boxes are checked for the Wait for Tape Mounts (for both the Restore and for File Information).
Depending on which database backup is being restored, it may be necessary to check the Show Active and Inactive box.
If this restore is going to replace an already existing database on this alternate restore machine, also make sure the Replace check box is selected.
7. On the left-hand side, under SQL Servers, open the list to see the SQL Server and databases from the originating (source) machine.
8. In the main restore window, select the database to be restored ( the box next to the database should have a red check mark).
9. Right click on this selected database and 3 options will be displayed: Restore Into, Relocate, Stand Server Undo File.
10. Both the Restore Into and Relocate options will need to be configured.
a. Select the Restore Into and enter the new name for this database on this alternate (target) restore machine.
b. Then select the Relocate, wait for the dialog box to appear that contains the information regarding the file location/name for the database backup. If the metadata containing this information must wait for a tape mount on the Tivoli Storage Manager Server, it may be some amount of time until the dialog box is populated. It is not possible to update the relocate information until after the box has been filled in with this File Information metadata.
11. If there are multiple objects (a full, differential, logs) selected for the restore, then the Restore Into will need to be specified for each, but the Relocate is only necessary for the full database backup as all the logs will be written based on the paths specified for the full.
12. Then click the Restore button to complete the restore to this alternate (target) restore machine.