Restoring an SQL Server database to an alternate instance

By using Microsoft Management Console (MMC), you can restore an SQL Server database backup to an alternate SQL Server instance or database. You can also restore availability databases to an alternate location on any availability replica in an availability group.

1. Copy the Tivoli Storage FlashCopy Manager for SQL Server options file (dsm.opt) from the source system to the target system.

   Source system

   The system from which the original backup (to be restored) is created.

   Target system

   The alternate system to which the backup is to be restored.

   By default, the dsm.opt file is in the C:\Program Files\Tivoli\TSM\TDPSql directory. If you specified a value of generate for the passwordaccess parameter in the dsm.opt file, you might need to reset the password for this node on the Tivoli Storage Manager server.
2. Start MMC.
3. On the Recover tab for the SQL instance, specify the type of SQL data to restore.

   Table 1. Database backup views

   Task	Action
   View a list of SQL databases that are available for a restore operation	Click All Backups.
   View a list of SQL Server 2012 and later version availability databases that are available for a restore operation	Click DP Node Backups to show AlwaysOn node backups. Toggle the DP Node Backups / AlwaysOn Node Backups button for the respective database views.

4. Verify the options for the restore operation. If the restore options are not displayed, click Show Restore Options.
   1. Ensure that Wait for Tape Mounts for Restore is set to Yes.
   2. Ensure that Wait for Tape Mounts for File Information is set to Yes.
   3. If the database to be restored is to replace an existing database on the target system, click Replace.
   4. Use the Instant Restore option to turn Instant Restore on or off. Click Yes to use Instant Restore. Click No to disable Instant Restore if you want to use Fast Restore.
      Attention: Instant Restore operations overwrite all files on the destination file system.
5. To start the backup operation, in the Actions pane, take one of the following actions:
   1. Click Restore to Alternate Location.
   2. Click Restore VerifyOnly to Alternate Location. The Restore VerifyOnly to Alternate Location task is available only if all the selected database backups are legacy backups.
6. In the Restore Into section of the Alternate Location Restore Settings window, click Restore to new database, and specify a target SQL Server instance name and target database name to restore a backup object to. VSS backups cannot be restored into an SQL Server that has a different name.
   Attention: Any type of Restore Into processing automatically disables VSS instant restore.
7. In the Relocate section of the window, filter the restore processing operations.

   Table 2. Database backup views

   Task	Action
   Specify new destination locations in which to restore backed up SQL databases, logs, and FILESTREAM files (SQL Server 2008 or later versions)	Click Restore all files into one directory.
   Restore the log files into a location that is different from where the SQL database and other related files are restored	Select Relocate logs into and specify a new path in the text entry field.
   Restore FILESTREAM files (SQL Server 2008 or later versions) into a location that is different from where the SQL database and logs are restored	Select Relocate other files into, and specify a new path in the text entry field.
   Restore one or more individual SQL database, log, and FILESTREAM files	Click Relocate files individually, and click Browse to open a folder selection window. Select a folder or create a new folder, and click OK. The path of the selected files entries is set to use the folder. This option is available for legacy backups only.

   Restriction: You cannot relocate database files and logs with a partial restore operation in MMC. You must use the command-line interface to complete a partial restore operation that requires these parameters.
8. Click Restore to close the Alternate Location Restore Settings window and begin the restore.
9. To view the status of the restore operation, click Task List in the lower half of the results pane. Click Task Details to view detailed status information.