Source:MS SQL Restore

From SEPsesam
Revision as of 09:02, 16 June 2020 by Sta (talk | contribs) (Marked this version for translation)
Other languages:

Copyright © SEP AG 1999-2024. All rights reserved.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3/4.4.3 Beefalo V2. For previous documentation version(s), check MS SQL.


Overview

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.

SEP Warning.png Warning
You can restore one or more databases at the same time only if you are restoring MS SQL databases to the original location. 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 an alternate location won't work with multiple databases selected.

Restore is triggered from the Menu bar -> Activities -> Restore. Restore wizard guides you through the process of restoring your data. For step-by-step procedure, see Standard Restore Procedure.

Information sign.png Note
SEP sesam provides the web Restore Assistant interface which is designed to be more intuitive and offers additional advanced options compared to GUI restore wizard while, on the other hand, it does not support the restore of special tasks types, such as MS SQL, PostgreSQL, Oracle, etc. For these task types you can only use the GUI restore wizard to restore your data.

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.

  1. From the SEP sesam GUI menu bar, select Activities -> Restore. The New Restore Task window opens.
  2. Select what you want to restore. You can search the savesets by task name or by filename or path.
  3. Under the Saved in period drop-down lists, specify the time frame for which you want to conduct the search. Click Next.
  4. The search results are displayed. From the list of savesets matching your query, click the version you want to restore. Click Next.
  5. MS SQL restore select task Beefalo V2.jpg
  6. In the Select Files dialog, select the data you want to restore and click Next.
  7. 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:
      • sbc -r -a recover -v 3 sbcmsql:<server_name>/<instance>/<DB_name>

    MS SQL restore target Beefalo V2.jpg

  8. 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.
Information sign.png 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 view the status of your restore jobs by selecting Job State -> Restores from the Main Selection. 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.

As of 4.4.3 Beefalo V2, you can also monitor the restore progress using the Web UI. For details, see SEP sesam Web UI.

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:

> GUI relocation (≥ 4.4.3.45 Tigon V2)

> move option (≤ 4.4.3.42 Tigon)

Information sign.png 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 (≥ 4.4.3.45 Tigon V2)

As of 4.4.3.45 Tigon V2, you can use the restore wizard to select the MS SQL database logical file name and configure relocated physical file name.

  1. From the SEP sesam GUI menu bar, select Activities -> Restore. The New Restore Task window opens.
  2. Select the database you want to restore, then select the desired version and choose the Complete restore of selected task option.
  3. 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.
  4. Information sign.png Note
    As of v. 4.4.3. Beefalo, the Expert Options button for specifying advanced restore options is only available in the Expert UI mode. It is not available for users that run the GUI in Basic or Advanced mode. In the latter case, if you want to access the Expert Options you first have to change the Basic/Advanced UI mode to Expert, as described in Selecting UI mode.
  5. 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 file path is listed.
    You have to specify a new path for your database files (as you do with the move option which is explained below). Simply enter (or browse for) a new path to the database and log file, e.g., SQL/MSSQLSERVER.
  6. Click OK to set a new path for the database, and then click Next.
    MS SQL restore relocation Beefalo V2.jpg
  7. To start your database restore immediately, click Start. Otherwise, save your restore task.
SEP Warning.png Warning

Pay special attention if you are using SEP sesam version 4.4.3.48 Tigon V2 on SEP sesam Clients with MS SQL Server. Note that MS SQL Server restore may overwrite the original database if your MS SQL Server version, such as MS SQL Server 2016, allows to overwrite an online database. A patch is available to fix this problem; see Release Notes 4.4.3 Tigon V2 for details.

By using the move option (≤ 4.4.3.42 Tigon)

For 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
SEP Tip.png 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.
Information sign.png 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.

  1. Back up the OriginalDB and make sure that the backup is successful.
  2. Open the SQL Server Management Studio and create a restore database named RestoreDB. In the create dialog, select a different database location.
  3. 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).
  4. 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).
  5. After you have created the RestoreDB, set the database offline.
  6. Open SEP sesam restore wizard and select the OriginalDB database for the restore.
  7. 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.
  8. Start the restore.
  9. 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.

  1. Stop MS SQL Server service.
  2. Only the files (.mdf and .ldf) of the system databases are restored from the last path backup to the original path with overwrite.
  3. Restart MS SQL Server service.
  4. 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.

  1. 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.
  2. 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

MS SQL Backup