Restoring an SQL Server database to an alternate instance

Edit online

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

Before you begin
Install IBM Storage Protect Snapshot for SQL Server on both systems.
About this task
You can also restore availability databases that you backed up with the AlwaysOn node. Backups of availability databases can be restored to any availability replica in an availability group.

You can select only one database at a time when you restore a database to an alternate location.

Procedure
  1. Copy the IBM Storage 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 Storage Protect server.
  2. Start MMC.
  3. On the Recover tab for the SQL instance, specify the type of SQL data to restore.
    Table 1. Table 1. Database backup views
    TaskAction
    View a list of SQL databases that are available for a restore operationClick All Backups.
    View a list of SQL Server 2012 and later version availability databases that are available for a restore operationClick 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. Table 2. Database backup views
    TaskAction
    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 restoredSelect 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 onlySelect 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 onlyClick 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.
What to do next
You can restore a legacy database backup that is verified as valid and complete with the Verify Only option in MMC, or with the /VERIFYOnly option of the restore command on the command line.