Restoring an SQL Server database to an alternative instance

Edit online

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 alternative 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).

Before you begin

Install Data Protection for SQL Server on both systems.

About this task

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 Data Protection 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 passwordaccess generate is specified 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 Server instance, specify the type of SQL Server data to restore.
    Table 1. Database backup views
    Task Action
    View a list of SQL Server 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. For a local restore operation, the local restore automatically fails over to Fast Restore.
      Attention: Instant Restore operations overwrite all files on the destination file system.
    5. If the original SQL instance is different than the selected instance in the MMC console, set From Sql Server to *. This setting shows all backups from all instances. Click Refresh to refresh the view after making the change.
  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. For a legacy backups, select the SQL Server instance which you want to restore to. 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 Server databases, logs, and FILESTREAM files Click Restore all files into one directory.
    Restore the log files into a location that is different from where the SQL Server 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 Server 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.
    Tip: When you restore a VSS backup to an alternate SQL Server instance, the Restore to original location processing option is disabled, However, it is still possible to restore to the original location by entering the same database name and database file path as in the original location.
    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.