Restoring SQL database into other database with TSM

Posted on by

Problem(Abstract)
SQL database restore back to the same SQL Server without overwriting the original database when using the Data Protection for SQL client  
   
Resolving the problem
The Data Protection for SQL will allow for a restore to an alternate location on the same SQL Server.
When using the Data Protection (DP) for SQL to restore a database to an alternate location (into a new database) on the same machine, both the RELOCATE and the RESTORE INTO parameters must be used.
The RELOCATE is used to determine what file on the hard drive will be used to hold the data. This will need to be changed to have a different name and/or location to be something different (test) than the production database.
The RESTORE INTO is used by the SQL Server to identify the database. This should be a new name so that the SQL Server will be able to differentiate between the production database and this second (test) copy of the database.

To use the Data Protection for SQL GUI client, the following steps can be used for this alternate restore.
1. Launch the DP SQL GUI.
2. Click on the Restore Databases tab.
3. Make sure that both boxes are checked for the Wait for Tape Mounts (for both the Restore and for File Information).
4. Depending on which database backup is being restored, it may be necessary to check the Show Active and Inactive box.
5. On the left-hand side, under SQL Servers, open the list to see the SQL Server and databases.
6. In the main restore window, select the database to be restored ( the box next to the database should be yellow and have a red check mark).
7. Right click on this selected database and 3 options will be displayed: Restore Into, Relocate, Standby Server Undo File.
8. Both the Restore Into and Relocate options will need to be configured.
Select the Restore Into and enter the alternate name for this database on the SQL Server.
Then select the Relocate, wait for the dialog box to appear that contains the information regarding the file location/name for the database backup and update these to be different than the existing database files. If the metadata containing this information must wait for a tape mount on the TSM 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.
9. Then click the Restore button to complete the restore.

5 thoughts on “Restoring SQL database into other database with TSM”

  1. Hi,

    Does this mean that you first need to create an empty DB, assign datafiles in SQL server and use this one to redirect to?

    Thanks!
    Dennis

  4. Good guide

    But I keep Geting

    “Restore failed. An exception occurred while executing transact-SQL statement or batch. the backup set holds a backup of a datebase othjer than the existing ‘Restore_xxx’ datebase. RESTORE LOG is terminating abnormally changed datebase context to ‘master’ (HRRESULT: 0x80131501)

    Any ides?

  5. Hi Niklas,

    Did you choose the option restore into? This kind of error normally comes when you try to overwrite a other database, for this you need a replace option (restore into). In SQL language i thought it was the following:

    RESTORE DATABASE AdventureWorks
    FROM DISK = 'C:\BackupAdventureworks.bak'
    WITH REPLACE

Leave a Reply

Your email address will not be published. Required fields are marked *