Restoring the Database and Log Backups with
the SQL Server Management Studio
...
1. Make sure that no users are connected to the database.
2. Connect to the SQL Server instance in the SQL Server Management Studio.
3. In the Object Explorer, select the server name to expand the server tree.
4. Expand Databases and select a user database.
5. Right-click the selected database, point to Tasks, and choose Restore.
6. Select Database.
The Restore Database dialog box opens.
7. In the To a point in time text box, either keep the default (the most recent possible), or select a specific date and time by clicking the Browse button, which opens the Point in Time Restore dialog box.
8. On the General page, the name of the restoring database appears in the To database list box. Select <SAPSID> as the name of the database.
To specify the source and location of the backup sets to restore, choose the From database option.
Enter the database you want to restore in this list box. When you choose this option, the dialog automatically fills up the backup sets list from the backups including the differential and transaction log backups taken for this database from the msdb backup history tables. If you want to restore the SAP database, select <SAPSID> as database.
If you want to restore from a device, choose Browse to open the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. Add one or more devices for the Backup location list box with Add.
After you have added the device(s), choose OK to return to the General page.
9. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups that are available for the specified database. By default, a recovery plan is suggested. This list is generated from the backup history that is stored in the msdb database. Check that the list contains all backups of the selected database, especially the last transaction log backup.
○ If the backup history in the msdb database is not up-to-date you have to restore the database and all succeeding transaction log backups from a device.
In this case, follow the instructions in Restoring the <SAPSID> Backup from a Device with SQL Server Management Studio.
○ If only the last transaction log backup is missing in the list of the backups suggested for the restore operation, proceed as follows:
i. Change the restore options in the Options page into:
Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored.
ii. Continue with steps 10 and 11.
iii. Set the restore option:
Leave the database ready to use by rolling back the uncommitted transactions. You cannot restore additional transaction logs
iv. Restore the last transaction log backup from a device, as described in Restoring the <SAPSID> Backup from a Device with SQL Server Management Studio.
10. Switch to the Options page and set the correct restore options:
¡ Overwrite the existing database
¡ Prompt before restoring each backup
After restoring the initial backup and before restoring each additional backup set, this option opens the Continue with Restore dialog box, where you are asked whether you want to continue the restore sequence. This dialog displays the name of the next media set (if available), the backup set name, and the backup set description.
This option is particularly useful, if you must swap tapes for different media sets. For example, you can use this option, if the server has only one tape device. Wait until you are ready to proceed before you choose OK.
Choosing No leaves the database in the restoring state. At your convenience you can continue the restore sequence after completion of the last restore. If the next backup is a data or differential backup, reuse the Restore Database task. If the next backup is a log backup, use the Restore Transaction Log task.
11. Insert the correct tape with the most recent database backup into the tape drive. If you originally backed up the database to several tapes in parallel, then you have to insert all the tapes used in parallel.
12. Switch back to the General page. The backups that must be restored are pre-selected. This can include the most recent database backup, the latest subsequent differential backup and all succeeding transaction log backups.
Make sure the most recent database backup has been selected. If a differential backup is selected, make sure it is the latest one in the list.
13. Choose OK to start the backup.
The data from the database backup is copied to the disk and the succeeding log backups are applied to the database.