IBM Tivoli Storage Manager for Databases, Data Protection for Microsoft SQL Server, Version 7.1

Restoring a SQL database to an alternate system

You can restore a SQL database backup to an alternate SQL Server system or database by using the MMC GUI. Data Protection for SQL Server must be installed on both systems. Also, unlike legacy backups, VSS backups cannot be restored into a SQL Server that has a different name.

Before you begin

You can also restore availability databases to an alternate location on any availability replica in an availability group.

About this task

This procedure uses the following terms:

Source system: The system from which the original backup (to be restored) was taken.
Target system: The alternate system to which the backup is to be restored.

When restoring availability databases, refer to the following guidelines:

Legacy restore

You can restore an availability database on either a primary or secondary replica.

During the restore process, the restored database is removed from the availability group. When a database is removed from the availability group, the database becomes a local database on that replica. The database is restored as a local database. After this restore is complete, manually add the database back to the availability group. However, before you add the database to the availability group, verify that the data on all replicas is transactionally consistent.

To verify data is transactionally consisent, verify that the backup copy contains data and transaction log records. Full backups and differential backups contain data and transaction log records so that the restored database is transactionally consistent.

After you verify that the data is transactionally consistent, the database can be added to the availability group.

VSS restore

Because of a SQL Server limitation, you cannot restore a VSS backup to an alternative SQL server instance. Therefore, VSS backups must be restored to the same SQL server instance where the snapshot was taken.

Procedure

To restore a SQL database to an alternate location:

Copy the Data Protection for SQL Server options file (dsm.opt) from the source system to the target system. 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 Tivoli® Storage Manager server.
Start the Management Console.
In the Recover tab for the SQL instance, select a database to restore. Optionally click All Backups in show all active and inactive backups. You can select only one database at a time when restoring it to an alternate location.
For availability databases, click DP Node Backups to show all AlwaysOn node backups. The name of the button changes to AlwaysOn Node Backups. The DP Node Backups / AlwaysOn Node Backups button toggles between the standard database view and the availability database view. The label reflects the type of databases that are displayed in the view.
Verify restore options. If the restore options are not currently displayed, click Show Restore Options.
1. Ensure that Wait for Tape Mounts for Restore is set to True.
2. Ensure that Wait for Tape Mounts for File Information is set to True.
3. If the database to be restored is going 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 True to use Instant Restore. Click False to disable Instant Restore if you want to use Fast Restore.
  Attention: Instant Restore overwrites all files on the destination file system.
See Restore options for descriptions of additional restore options.
Click Restore to Alternate Location in the Actions pane.
Complete the Alternate Location Restore Settings window.
- In the Restore Into section of the 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 target database must exist. VSS backups cannot be restored into a SQL Server that has a different name.
  Attention: Any type of Restore Into processing automatically disables VSS Instant Restore.
- If you want to specify new destination locations in which to restore backed up SQL databases, logs, and FILESTREAM files (SQL Server 2008, SQL Server 2008 R2, or later), click Restore all files into one directory in the Relocate section of the window.
  If you want to restore the log files into a location that is different from where the SQL database and other related files are being restored, select Relocate logs into and specify a new path in the text entry field.
  
  If you want to restore FILESTREAM files (SQL Server 2008, SQL Server 2008 R2, or later) into a location that is different from where the SQL database and logs are being restored, select Relocate other files into, and specify a new path in the text entry field.
- If you want to restore one or more individual SQL database, log, and FILESTREAM files, click Relocate files individually, select one or more file entries, and click Browse to open a folder selection window. Select a folder or make 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 partial restore operation in the Management Console (MMC) GUI. You must use the command-line interface to do a partial restore that requires these parameters.
Click Restore to close the Alternate Location Restore Settings window and begin the restore.

What to do next

You can view the status of the restore operation by clicking Task List in the bottom half of the results pane. Click Task Details to view detailed status information.

Feedback