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

Restoring SQL server data

You can restore SQL server data.

Before you begin

You can restore databases or parts of databases only from full, differential, and log backups. VSS supports only full backups. Legacy differential and legacy log backups can be applied after a full VSS backup is restored.

A legacy or VSS restore of the master database requires a different procedure. For more information, see Restoring the master database.
When Virtual Environment restore is configured from the Tivoli® Storage Manager server, you can restore and view these databases from the Recover tab.

Attention: When you restore a database, existing data is overwritten by the restored data and is no longer available after the restore is complete.

You can also restore availability databases that you backed up with the AlwaysOn node in a SQL Server 2012 environment. Backups of availability databases can be restored to any availability replica in an availability group.

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 an SQL server database:

Start the Management Console.
Select the SQL Server instance in the tree.
On the Recover tab for the SQL instance, specify the type of SQL data to restore:
- Select View: Databases for a list of SQL database backups that are available for restore.
  If you want to view Tivoli Storage Manager server for Virtual Environment SQL database backups, open the Properties page, and select Data Center Node. Select the check box, IncludeTSMVM. Virtual Environment databases are listed with the TSMVM backup method.
- Select View: Files for a list of SQL database backup files that are available for restore.
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.
Use the Recover tab to browse and select the databases or files to restore. The following features are available:
- Search: Fine-tune the list of available databases or files in the results pane by entering a keyword in the Search field.
- Filter: Use the filter options to narrow the list of items in the result pane.
  1. Click Show Filter Options and Add Row.
  2. Click the down arrow in the Column Name field and select an item to filter.
    When you click Select All, all rows that reflect the filter specifications are selected.
  3. Select an operator in the Operator field.
  4. Specify a value to filter on in the Value field.
  5. If you want to filter on extra items, click Add Row.
  6. Click Apply Filter to filter the items on the list.
- Backups: You can click Active Backups to show only active backups, or click All Backups to show both active and inactive backups.
- Refresh: Click Refresh to update the view with your changes.
  If you applied a filter, the objects on the server that match the filter or search criteria are listed in the Recover tab. The status area indicates the number of items that match the criteria n of x displayed, where n equals the number of objects that match the filter criteria, and x is the number of objects that are retrieved from the server. For example, "5 of 20 displayed." If you specify refresh options to further narrow your results, and click Refresh again, the objects on the server that match the filtered and refresh options are displayed. Each time that you click Refresh, another query is run against the Tivoli Storage Manager server.
Verify the restore options. If the restore options are not displayed, click Show Restore Options.
- Select the Instant Restore option to turn Instant Restore on or off. Disable the Instant Restore function if you want to use Fast Restore.
  Attention: Instant Restore overwrites all files on the destination file system.
Optional: Choose a mode for the current task:
- Run Interactively: Click this item to run the current task interactively. This selection is the default.
- Run Scheduled: Click this item to convert the current action into a scheduled task. When you select this item, the schedule wizard begins, complete with the command that is required to complete the task.
Click Restore in the Actions pane to begin the restore operation.

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.

During a restore operation, the operation might fail with a message similar to the following message:

Failed - An exception occurred while executing a Transact-SQL statement 
or batch.
The tail-log backup of the dbName database has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you 
do not want to lose.
Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to 
overwrite the contents of the log.

RESTORE DATABASE is terminating abnormally.
Changed database context to 'master'. (HRESULT:0x80131501)

When this situation occurs, set the backup tail of the log option to True. After you set this option, select Log Backup to TSM to complete the tail-log backup. For information about tail-log backups, refer to the Microsoft SQL Server documentation.

Feedback