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. You can restore to an alternate instance by using Microsoft Management Console (MMC), Windows PowerShell cmdlets, or the command-line interface (CLI).

1. Copy the IBM Spectrum Protect Snapshot 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 IBM Spectrum Protect 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.
      Important:

      When you select the Restore VerifyOnly to Alternate Location action, the stripes number must be the same or greater than that which is set in the backup. If it is not, the Restore VerifyOnly operation terminates with an error.

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. The Database name can have the same name as the source database or you can specify a different unique name.

   For VSS backups, the only instance available to restore to is that which you select on the Recover tab before starting the backup operation.

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 relevant for legacy restore operations only	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 relevant for legacy restore operations only	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.