IBM Support

Restoring an SQL database to an alternate location on the same machine

Question & Answer


Question

Can an SQL database be restored to the original SQL Server without overwriting the original database when using the Data Protection for SQL client

Cause

The Data Protection for SQL will allow for a restore to an alternate location on the same SQL Server.

Answer

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.
For an alternate restore it is important to ensure that the existing databases/files on this machine are not overwritten. Thus you need to ensure that the names/values used for the RELOCATE and the RESTORE INTO are set to something different than any exiting databases on this machine.
  • If only the Relocate is changed, then a different file name is used to hold the data on the hard drive, but the database name is the same. This causes the original database to just point to new file locations.
  • If the RestoreInto is used, but not the relocate, this will give a new name to the database, but overwrite the original data files on the harddrive.

Thus for an alternate restore both the RELOCATE and RESTORE INTO parameters must be set correctly.
[{"Product":{"code":"SSTFZR","label":"Tivoli Storage Manager for Databases"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Protection for MS SQL","Platform":[{"code":"PF033","label":"Windows"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Product Synonym

TSM

Document Information

More support for:
Tivoli Storage Manager for Databases

Software version:
Version Independent

Operating system(s):
Windows

Document number:
387813

Modified date:
17 June 2018

UID

swg21385555

Page Feedback