Table of Contents:
18.5.1.1 Restore master database 18.5.1.2 Restore other system or user database (complete backup) 18.5.1.3 Restore other system or user database (differential / transaction log backup)
18.5.1.1 Restore master database
Refer to the following instructions to restore the master database:
- Stop the SQL instance.
- Restart the SQL instance in single-user mode.
From a command prompt, browse to the appropriate directory for the instance of SQL to start
Enter the following command.
For default instance >sqlservr.exe -c -m For named instance >sqlservr.exe -c -m {instance_name}Important: Only perform Step 1 and 2 if you are restoring the master database.
- Open SQL Server Enterprise Manager; connect to the server where the backup is to be restored.
- Right click the master database and select [All Tasks], then select [Restore database].
- Select the [From device] radio button and [Select Devices].
- In the [Choose Restore Devices] dialog box, select the [Disk] radio button.
- Click the [Add] button to display the dialog box, enter the file name of the backup file (restored from the OBS) that is to be restored.
- Click the Options tab and select appropriate options for the restore operation.
- Click OK in the Restore Database dialog box to begin the restore process.
- Restart the SQL instance in multi-user mode afterward.
Restart the SQL Windows services, or enter the following command.
For default instance >sqlservr.exe -c For named instance >sqlservr.exe -c {instance_name} - Continue with the following steps to restore other system database and the user databases.
If the file exists on a network share, provide the UNC share name in the File name text box.
18.5.1.2 Restore other system or user database (complete backup)
Refer to the following instructions to restore a complete backup:
- Open SQL Server Enterprise Manager; connect to the server where the backup is to be restored.
- Right click the corresponding database and select [All Tasks], then select [Restore database].
- Select the [From device] radio button and [Select Devices].
- In the [Choose Restore Devices] dialog box, select the [Disk] radio button.
- Click the [Add] button to display the dialog box, enter the file name of the backup file (restored from the OBS) that is to be restored.
- Select the [Restore backup set] radio button, and [Database - complete].
- Click the Options tab and select appropriate options for the restore operation.
- To start the restore operation, click OK.
- Continue to the next section to restore differential or transaction log backup.
Note:
SQL Server databases may be backed up on one server and restored to another server.
If the target database does not exist on the server, right click on any database instead. A new database can be created while restoring the backup.
If the file exists on a network share, provide the UNC share name in the File name text box.
For the [Recovery completion state] setting:
Important:
If this is the final backup to be restored, select - [Leave database operational.
No additional transaction logs can be restored]
If there are further differential or transaction log to be applied, selection one of the other two options for the Recovery completion state
18.5.1.3 Restore other system or user database (differential / transaction log backup)
Refer to the following instructions to restore a differential or transaction log backup:
- Open SQL Server Enterprise Manager.
- Right click the corresponding database, select [All Tasks], and then select [Restore database].
- In the window that is displayed, select the [From device] option.
- Add the backup device (restored from the OBS), refer to the procedure explained in previous section.
- Select [Database - differential] or [Transaction log] according to the backup file you are restoring.
- Click the Options tab and select appropriate options for the restore operation.
- To start the restore operation, click OK.
Important:
If this is the final transaction log to be applied, select -
[Leave database operational. No additional transaction logs can be restored]
If there are further backups to be applied afterward, one of the other two options for the Recovery completion state.