This article describes how to restore one or more MS SQL databases online. For details on how to restore MS SQL databases by changing the database name and the disaster recovery procedure, see MS SQL Restore in the GUI.
Now it is possible to restore MS SQL Server databases using the web interface Restore Assistant if you have been granted the appropriate permissions. For details on its requirements, see Restore Assistant.
SEP sesam allows complete or selective restore of MS SQL databases to the original or to a new location with relocation. In advanced UI mode, you can also write backups to dump files.
Before attempting to restore MS SQL databases, prerequisites must be met and all preparatory steps must be performed properly, including configuration and backup. For details, see MS SQL Backup.
Accessing the restore assistant
You can access the restore assisstant in one of the following ways:
- via the GUI: by clicking the Restore Assistant icon in the toolbar or from Activities -> Restore Assistant
- from SEP sesam Web UI: left menu -> Restore Assistant
- or by entering the following address in the browser bar: http://[sesamserver]:11401/sep/ui/restore/.
Restoring MS SQL Server databases online
With the web Restore Assistant, you can restore MS SQL backups to the file system by performing a complete or selective restore. When restoring to the original location, you have to select one of the appropriate options to overwrite existing items. This means that a database that may already exist on the target server will be replaced with the restored version. To avoid overwriting the existing database, you can restore a database to a new location.
|You can restore one or more databases at a time if you restore MS SQL databases to the original location. If you are restoring MS SQL databases to a new location, you can only restore one database at a time.|
If you switch from basic to advanced restore mode (click the Advanced View option), you can also write backups to dump files. Note that the basic restore mode is enabled by default. For more details, see Setting UI mode.
Performing complete or selective restore
Complete and selective restore procedures are almost identical, except that selective restore requires you to select one or more databases. When you restore MS SQL databases in advanced UI mode, an additional Options tab is available where you can set advanced restore options.
- Open the Restore Assistant in the browser.
- In the Start window, select Microsoft SQL Server. If you want to perform MS SQL restore in the advanced UI mode, you also have to select Advanced View and then Restore of SQL Server databases. Click Next.
- In the MS SQL Server window, select your MS SQL client.
- In the Task window, under the Task selection, select your backup task. A backup task defines the source data that was backed up by the client.
- If you are performing a selective restore, select a single database in the Files window and click Next.
- In the Target window, first select the target client for the restore. You can filter the clients by name, location, and OS.
- The Restore to the original target path option is enabled by default. Skip this option to restore the files to the original location. Uncheck this option if you want to restore your data to a new restore target with relocation, and specify the new target path; you can type or browse the path where you want to restore your data.
- Under the Execution options, you can set additional restore options:
|You can search for a file or directory by entering your search term in the Search for files or directories in all backups field.|
Under the Backup selection, select the exact backup version you want to restore. You can use the calendar function in the upper right corner to set a date range for the backups.
Then select whether you want to perform a selective or complete restore and click Next. Note that a selective restore requires an additional step. For a complete restore, you are immediately taken to step 5 (Target tab).
- Do not overwrite existing items: The data is restored only if it does not already exist on the target system.
- Overwrite existing items: If the data exists on the target server, it is replaced with the restored version. This option must be selected if restoring to the original location.
- Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. Additionally, you can also preserve the replication settings and/or restrict access to the restored database.
- Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.
- Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed. In this case, you must also specify the name of the file Standby File.
Click Next. Note that if you are restoring in the advanced mode, you can set additional restore options in the next step (Options tab). In basic restore mode, you will be taken to the last step immediately.
The Include/ Exclude Filter tab allows you to specify which files or directories you want to include or exclude from the restore, for example, enter *.docx in the appropriate filter to include or exclude all MS Word *.docx files from the restore. You can use the include or exclude filter on the client-side or the exclude filter on the server-side. The latter is not available for complete restores.
Under the Advanced restore options, you can further refine your restore:
- Use the Log, Special Options tab to change the log level for your specific restore, see Setting Log Level. You can specify additional commands that may be useful for specific options of the sbc command. For details about the commands, see SBC CLI.
- Use the Retention, Generation, Pre/Post tab if you want to specify the retention period parameter for the restore (how long (in days) the restore task is kept), enable/disable a generation restore, and specify whether to apply a pre- or post-script to the restore task, see Pre/Post options.
When you have set everything, click Next.
Restoring MS SQL databases by writing the backups to dump files
When you start MS SQL Server web restore, you can also write the backups to dump files and restore the data to a single file rather than to its original location. In this case, you must specify a restore destination path in the Target window (by browsing or typing the path). Optionally, you can also change the name of the dump file. If the dump file name is not specified, it is generated automatically.
Note that you can also use the same option with an identical procedure when restoring files, directories, or emails in the advanced UI mode.
- Open the Restore Assistant in the browser.
- In the Start window, select Microsoft SQL Server. Then select Advanced View and Write backups into dump files. Click Next.
- Under the Backup selection, select the exact backup version you want to restore. You can use the calendar function in the upper right corner to set a date range for the backups. You can also filter the backups by States and/or Event types, and search for specific tasks or clients. Note that for MS SQL backups, the Generation Restore option is enabled by default. Click Next.
- In the Target window, specify the following target options:
- Select the target client for the restore.
- Specify a target path for the restore (by browsing or typing the path). Optionally, specify a name for the dump file. If the dump file name is not specified, it is generated automatically.
- Select the appropriate Execution option. Note that the options depend on the backup selected, but generally the following options are available: Do not overwrite existing items: The database will not be restored if it already exists on the set target.
Create new version: The files will be restored under a new name.
Overwrite existing items: If the data exists on the target server, it will be replaced with the restored version.
Skip ACL settings during restore: If this option is selected, the preconfigured ACL settings are skipped during restore.
When you have completed all the settings, click Next.
You can monitor the restore progress using the Web UI (Monitoring -> Restores) or view the status in the GUI (Main Selection -> Job State -> Restores). The restore overview provides detailed information on the last run of restore jobs, including task name, status (successful, error, in queue ...), start and stop time of the last backup, data size, throughput, etc. For details, see SEP sesam Web UI or Restores by State in the GUI.