Restoring the <SAPSID> Backup from a Device with SQL Server Management Studio

...

Use

Use this procedure if the msdb database is not up to date and you therefore cannot perform a normal restore using the backup history of msdb tables.

Depending on the overall restore procedure that has to be carried out, you may have to repeat the procedure many times. It is very important that the backup tapes are labeled correctly while the backup was performed, and the tapes are inserted into the tape device during this restore procedure in the correct sequence so that the restore operation will be successful.

Process Flow

       1.      If your database fails, try to back up the last active transaction log.

       2.      You restore the most recent full backup without recovering the database.

You use the following restore option:

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.

       3.      If a differential backup exists, you restore the most recent differential backup without recovering the database. You use the following restore option:

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.

       4.      You restore all transaction log backups that you created since the last full backup or differential backup (if available) without recovering the database. You restore the log backups in a sequence beginning with the eldest log backups except the last active transaction log (if available). If the last active transaction log is available, set the following restore option:

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.

If the last active transaction log backup is not available, set the following restore option:

Leave the database ready to use by rolling back the uncommitted transactions. You cannot restore additional transaction logs.

       5.      If you were able to back up the last active transaction log backup, set the restore option:

Leave the database ready to use by rolling back the uncommitted transactions. You cannot restore additional transaction logs, and restore the active transaction log.

Procedure

       1.      Insert the tape with the current backup in the tape drive.

       6.      Make sure that no users are connected to the database.

       7.      Connect to the SQL Server instance in the SQL Server Management Studio.

       8.      In the Object Explorer, select the server name to expand the server tree.

       9.      Expand databases, and right-click the <SAPSID> database.

   10.      Point to Task and choose one of the following options:

Ў        Restore ® Database to restore the full or differential database

Ў        Restore ®Transaction Log to restore the transaction log.

   11.      Switch to the Options page and set the following options:

Overwrite the existing database.

Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.

If the actual restore is the last in the overall restore process, set the restore options to Leave the database ready to use by rolling back the uncommitted transactions.

You cannot restore additional transaction logs.

   12.      Switch to the General page and specify that you want to restore From Device.

   13.      If the backup device is not listed in the text box, choose the Browse button, which opens the Specify backup dialog box. In the Backup Media list box, select one of the listed device types. Select your device and choose Add the device to the list box.

   14.      After adding the device(s) to the backup location list box, choose Ok to return to the General page.

   15.      Check the backup sets in the list, and check that the <SAPSID> database is specified.

   16.      Start the restore operation with OK.

Result

The current backup is restored from a device.