5 1 0:MS SQL Restore
Overview
This article provides information about a MS SQL Restore using GUI. For details on restoring with the web Restore Assistant, see Web MS SQL Restore.
For details on restoring AOAG databases, see Support for MS SQL AlwaysOn Availability Groups.
MS SQL Server backup and restore options depend on the preselected database recovery model in the SQL Server Management Studio, as described in section Selecting a recovery model for your MS SQL database.
The following three recovery models are available: simple, full, and bulk-logged. Typically, a database uses either full or simple recovery model, however, SEP recommends using full recovery model. You can switch the database to another recovery model at any time. For detailed explanation, refer to Microsoft article Recovery Model Overview.
Restoring MS SQL databases
SEP sesam allows for restoring the MS SQL databases to the original or to a new location. When restoring to the original location, e.g., to perform disaster recovery, you have to select the option to Overwrite existing items as described in section Restoring to the original location. This means that a database, which may already exist on the target server, will be replaced by the restored version. To avoid overwriting the existing database, you can restore a database to a new location.
Warning | |
|
For details on restoring AOAG databases, see Support for MS SQL AlwaysOn Availability Groups.
Restore interfaces
- There are two ways to restore MS SQL in SEP sesam: using the GUI restore wizard (described here) or via the web interface Restore Assistant (see Web MS SQL Restore). Although most options are the same in both restore interfaces, the web Restore Assistant is more intuitive and offers additional advanced options.
- You can only use the GUI to schedule a restore task (Scheduling -> New -> New Restore Event or Scheduling -> right-click schedule or event -> New Restore Event). For details, see Scheduling Restore.
This article provides information about a MS SQL Restore using GUI. For details on restoring AOAG databases, see Support for MS SQL AlwaysOn Availability Groups.
Restoring MS SQL databases to the original location
When restoring MS SQL database to the original target path (the same as from where it was backed up), you can restore one or more databases at the same time. Note that the existing database files are overwritten when the original target path is used.
Create a new restore task for the MS SQL database(s) you want to restore.
- From the SEP sesam GUI menu bar, select Activities -> Restore. The New Restore Task window opens.
- Select what you want to restore. You can search the savesets by task name or by filename or path.
- Under the Saved in period drop-down lists, specify the time frame for which you want to conduct the search. Click Next.
- The search results are displayed. From the list of savesets matching your query, click the version you want to restore. Click Next.
- In the Select Files dialog, select the data you want to restore and click Next.
- Under the Target Settings (previously Save and Start), set additional options.
- Under the Target path settings, select Restore to original target path (set by default). By selecting this option the data will be restored to the same location from which it was backed up.
- Under the Execution options drop-down list, select the following options:
- Select Overwrite existing items. If a database already exists on the target server, the existing database files are overwritten.
- From the second drop-down list, select Auto recover after restore to store the data directly into the database without caching in the file system; as the data is written directly into the database, it is not possible to modify it.
If you want to modify the data before you import it into the database, you may select No recover after restore. The restored data is stored on the file system rather than written over an existing database; in this case, you need to import the data into the database manually by using the command line on the server. Note that you can still recover the MS SQL database once the restore is completed by using the following command: - In the final step of the restore wizard, you can review all the settings of your restore task. You can also edit the settings by using Change Selection button. If you want to start your restore immediately, click Start. If you want to save the restore task, click Save.
sbc -r -a recover -v 3 sbcmsql:<server_name>/<instance>/<DB_name>
Note | |
A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see Scheduling Restore. |
You can monitor the restore progress using the Web UI (Monitoring -> Restores) or view the status in the GUI (Main Selection -> Job State -> Restores). Restore overview provides detailed information on the last run of restore jobs, including the task name, status (successful, error, in queue...), start and stop time of the last backup, data size, throughput, client and message. For details, see SEP sesam Web UI or Restores by State in the GUI.
Restoring a database to a new location by using GUI relocation or move option
If you want to restore an MS SQL database to the same machine, but to a different database name and location without affecting the original database, you can use one of the following two options depending on your SEP sesam version:
Note | |
When restoring MS SQL databases to a new location, you can only restore one database at a time although the GUI allows to select multiple databases. Restoring database to a new target path won't work with multiple databases selected! |
By using GUI restore wizard relocation
Use the restore wizard to select the MS SQL database logical file name and configure relocated physical file name.
- From the SEP sesam GUI menu bar, select Activities -> Restore. The New Restore Task window opens.
- Select the database you want to restore and then select the desired version.
- In the Target Settings dialog (previously Save and Start), leave the options as they are set by default and only select New restore target under the Target path settings. Then click the Expert Options button.
- In the new Restore: Expert Options window, switch to the Relocation tab and select With relocation option.
In the field Will be restored directly in the following target path, the original path of the physical file is listed.
If the original database is still running or it must not be overwritten, you have to modify the physical path by clicking New and specifying a new path for each logical name. - To start your database restore immediately, click Start. Otherwise, save your restore task.
Note | |
The Expert Options button for specifying advanced restore options is available only in advanced UI mode (formerly expert GUI mode). To use Expert Options, make sure your UI mode is set to advanced. For details, see Selecting UI mode. |
Note | |
If the original database does not run and the relocation is not configured, the files will be overwritten even if you have previously specified a new database name. |
Click OK to set a new path for the database, and then click Next.
By using the move option (older versions: ≤ 4.4.3.42)
For older versions (≤ 4.4.3.42 Tigon) the move option must be used to relocate each of the database files and avoid collision with existing files.
If the database was created with a different logical file name, you can restore it by using the move to clause (command) or by changing the database name in the SQL Server Management Studio. When restoring by using the move option, the logical file names of the target database are adjusted.
Prerequisites
- Before restoring a database to a new location, make sure that the database is offline. If a database is in use, e.g., when Open Table in the SQL Server Management Studio is used, the restore fails.
- When using the move option, make sure that the specified target directory name already exists. Pay attention not to overwrite the database files by mistake!
Use the move option as follows:
-a move={<original_logical_name>}:"<new_file_name_mdf>" -a move={<original_logical_name_ldf>}:"<new_file_name_ldf>"
Example
osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2 name physical_name state_desc ----------------------------------------------------------------------------------------------------------------------- sesam_db2 D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf ONLINE sesam_db2_log D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf ONLINE
Tip | |
You can use the restore wizard and enter the move option ''-a move=.:.'' as text (without changing lines) by using Expert Options -> tab Option -> Restore options field. |
Note | |
If the SEP sesam Server is working with a PostgreSQL database, e.g., on Linux x64, you have to enter the \- character twice, if not entered twice it disappears. |
Example 1: Restoring the backup of the database sesam_db to sesam2 by using move.
sbc -r -s @sesam_db.save -o over -a recover -a move=sesam_db:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf" -a move=sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam2_db2_log.ldf" sbcmsql:"/MIRACULIX/SECOND/sesam2"
Example 2: After the restore the adjusted logical file names will appear as follows:
osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2 name physical_name state_desc ----------------------------------------------------------------------------------------------------------------------- sesam_db D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf ONLINE sesam_db_log D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf ONLINE
Once the restore is completed, you should check the status of your restore jobs by selecting Job state -> Restores from the Main selection, or check the Error Log under Logging to make sure that the restore has completed successfully.
Restoring a database to a new location by changing the database name
If the database was created with a different logical file name, it is possible to restore it by using either the move to clause (command), as described in the section above, or by changing the database name in the SQL Server Management Studio, as described below.
In our example, the original database name is OriginalDB and the restore database name is RestoreDB.
- Back up the OriginalDB and make sure that the backup is successful.
- Open the SQL Server Management Studio and create a restore database named RestoreDB. In the create dialog, select a different database location.
- Rename the default database logical names (RestoreDB and RestoreDB_log) to original database logical names which should be restored (in our example, OriginalDB and OriginalDB_log).
- Rename the default database file names (RestoreDB.mdf and RestoreDB_log.ldf) to the original database file names which should be restored (in our example, OriginalDB.mdf and OriginalDB_log.ldf).
- After you have created the RestoreDB, set the database offline.
- Open SEP sesam restore wizard and select the OriginalDB database for the restore.
- In the Target Settings dialog (previously Save and Start), select the New restore target option and enter the new target path, e.g.,
W2K8R2SQL/MSSQLSERVER/RestoreDB
. Under the Execution options, select options Overwrite existing items and Auto recover after restore. - Start the restore.
- Once the restore is completed successfully, set the RestoreDB database online.
Disaster recovery
In some cases you have to perform a disaster recovery, e.g., if MS SQL Server is installed but the MS SQL Server service is no longer running. This can be caused due to different reasons, e.g., because all database files including the system databases are corrupted.
Note that the following section describes two possible solutions to perform the MS SQL disaster recovery. To learn more about preparation for MS SQL disaster recovery and get step-by-step instructions to rebuild databases, refer to official MS SQL documentation.
1st scenario: MS SQL backups and the database files are available
In addition to MS SQL backups, the database files (including system databases) are available in a VSS path backup.
- Stop MS SQL Server service.
- Only the files (.mdf and .ldf) of the system databases are restored from the last path backup to the original path with overwrite.
- Restart MS SQL Server service.
- You can now restore single DBs from the last MS SQL backup.
2nd scenario: Only MS SQL backups are available
Only MS SQL backups exist, but there are no database files in a path backup.
- Create a new MS SQL instance with the MS SQL setup files. Note that the setup files must be the same as the ones the MS SQL Server was originally installed with.
- You can now restore single DBs to the newly created instance.
Restoring MS SQL databases from the command line
You have to be a system administrator to be able to perform CLI restore. You can restore to the original location, to the identical location or to a new location (instance).
Restoring to the original location
In the following example, the database is restored to the original location with the options overwrite and recover:
sbc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:<server_name>/<instance>/<DB_name>
If the database is online during the restore, no changes will be shown. In case that no changes are shown, set the database offline and online again to be able to check the restore status.
Restoring to the identical location
If you are performing a restore to the identical location as the original database location and with the identical logical file names, proceed as follows:
sbc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:"/<server_name>/<instance>/<DB_name>"
In our example:
sbc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:"/MIRACULIX/SECOND/sesam_db"
You have to check the location and logical file names by using SQL Server Management Studio or by specifying the following command, where the name means logical file name:
osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam_db name physical_name state_desc ---------------------------------------------------------------------------------------------------- ------------------- sesam_db D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db.mdf ONLINE sesam_db_log D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db_log.ldf ONLINE
For MS SQL Server 2000 you have to use the following command to check the location and logical file names, for example:
osql -E -S COSINUS\ZWEITE_DB -Q "select * from sysfiles" -d sesamdb
You can retrieve all logical file names by using the command:
osql -E -S w2003enterprise -Q "select name, filename from sysdatabases" -d master
See also
Support for MS SQL AlwaysOn Availability Groups – Web MS SQL Restore – MS SQL Backup