Source:MS SQL Restore: Difference between revisions

From SEPsesam
(Minor update proposed by THU.)
m (fixing formatting errors)
 
(13 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<translate><!--T:1-->
<noinclude><div class="noprint"><languages /><br />
<div class="noprint"><languages />
{{Copyright SEP AG‎|en}}
{{Navigation_latest|release=[[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3/4.4.3 ''Beefalo V2'']]|link=[[Special:MyLanguage/MS_SQL|MS SQL]]}}</div></translate><br />


<translate>==Overview== <!--T:2--></translate>
<translate>==Overview== <!--T:2--></translate>
<noinclude><div class="boilerplate metadata" id="Additional resources" style="background-color: #f0f0f0; color:#636f73; border: 1px ridge #cdd3db; margin: 0.5em; padding: 0.5em; float: right; width: 35%; "><center><b><translate><!--T:3--> Additional resources</translate></b></center>


{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
</div></noinclude><span style="color:gray; font-size: 90%"><translate><!--T:138--> This article provides information about a ''MS SQL Restore using GUI''. For details on restoring with the web ''Restore Assistant'', see [[Special:MyLanguage/5_0_0:Web_MS_SQL_Restore|''Web MS SQL Restore'']].<br />For details on restoring AOAG databases, see [[Special:MyLanguage/Support_for_MS_SQL_AlwaysOn_Availability_Groups|Support for MS SQL AlwaysOn Availability Groups]].</translate></span>
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:4--> [[File:SEP_next.png|45px|link=Special:MyLanguage/4_4_3:MS_SQL_Backup]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | <translate><!--T:5--> See also: [[Special:MyLanguage/4_4_3:MS_SQL_Backup|MS SQL Backup]]</translate>
|}


{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:6--> [[File:SEP Tip.png|45px|link=Special:MyLanguage/FAQ#VSS_functionality|FAQ]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" |
<translate><!--T:7--> Check [[Special:MyLanguage/FAQ#VSS_functionality|FAQ]] to find the answers to most common questions.</translate>
|}
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:8--> [[File:SEP Troubleshooting.png|45px|link=Troubleshooting_Guide#MS_SQL]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | <translate><!--T:9--> Problems? Check the [[Special:MyLanguage/Troubleshooting_Guide#MS_SQL|MS SQL troubleshooting]].</translate>
|}
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:10--> [[File:icon_archived_docs.png|45px|link=MS_SQL]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | <translate><!--T:11--> If you are using an older SEP sesam version, refer to [[Special:MyLanguage/MS_SQL|MS SQL archive]].</translate>
|}</div></noinclude>
<translate><!--T:12-->
<translate><!--T:12-->
MS SQL Server backup and restore options depend on the preselected database recovery model in the SQL Server Management Studio, as described in section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]].
MS SQL Server backup and restore options depend on the preselected database recovery model in the SQL Server Management Studio, as described in section [[Special:MyLanguage/MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]].


<!--T:13-->
<!--T:13-->
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 [https://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx Recovery Model Overview].  
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 [https://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx Recovery Model Overview]. </translate>


=={{anchor|restore}}Restoring MS SQL databases== <!--T:14-->
=={{anchor|restore}}<translate><!--T:14-->
Restoring MS SQL databases==


<!--T:15-->
<!--T:15-->
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 [[Special:MyLanguage/4_4_3:MS_SQL_Restore#original|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.</translate>
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 [[#original|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.</translate>


{{<translate><!--T:16--> warning</translate>|
{{warning|
*<translate><!--T:17--> 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.</translate>
*<translate><!--T:17--> 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.</translate>
*<translate>In the case of an '''encrypted backup of multiple MS SQL databases''', it is not possible to restore a single database, only all databases together. Therefore, if you want to use encryption, you should '''create a separate job for each database'''.</translate>}}
*<translate><!--T:137--> If you have an '''encrypted backup of multiple MS SQL databases''', it is not possible to restore a single database, only all databases together. If you want to use encryption, you should create a '''separate job for each database''' to enable a smooth restore.</translate>}}


<translate><!--T:18-->
<translate><!--T:139-->
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 [[Special:MyLanguage/Standard_Restore_Procedure|Standard Restore Procedure]].</translate>
For details on restoring AOAG databases, see [[Special:MyLanguage/Support_for_MS_SQL_AlwaysOn_Availability_Groups|Support for MS SQL AlwaysOn Availability Groups]].


{{<translate><!--T:131--> Note</translate>|<translate><!--T:132--> 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.</translate>
=== Restore interfaces === <!--T:140--></translate>  
}}


<translate>==={{anchor|original}}Restoring MS SQL databases to the original location=== <!--T:19-->
*<translate><!--T:141--> 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 [[Special:MyLanguage/Web_MS_SQL_Restore|''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.</translate>
*<translate><!--T:142-->
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 [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].
 
<!--T:143-->
This article provides information about a ''MS SQL Restore using GUI''. For details on restoring AOAG databases, see [[Special:MyLanguage/Support_for_MS_SQL_AlwaysOn_Availability_Groups|Support for MS SQL AlwaysOn Availability Groups]].</translate>
 
==={{anchor|original}}<translate><!--T:19-->
Restoring MS SQL databases to the original location===  


<!--T:20-->
<!--T:20-->
Line 59: Line 45:
<li><translate><!--T:24--> Under the '''Saved in period''' drop-down lists, specify the time frame for which you want to conduct the search. Click '''Next'''.</translate></li>
<li><translate><!--T:24--> Under the '''Saved in period''' drop-down lists, specify the time frame for which you want to conduct the search. Click '''Next'''.</translate></li>
<li><translate><!--T:25--> The search results are displayed. From the list of savesets matching your query, click the version you want to restore. Click '''Next'''.</translate></li>
<li><translate><!--T:25--> The search results are displayed. From the list of savesets matching your query, click the version you want to restore. Click '''Next'''.</translate></li>
<translate><!--T:26--> [[Image:MS_SQL_restore_select_task_Beefalo_V2.jpg|800px|link=]]</translate>
<translate><!--T:26--> [[Image:MS_SQL_restore_select_task_Apollon.jpg|700px|link=]]</translate>
<br clear=all>
<br clear=all>
<li><translate><!--T:127--> In the ''Select Files'' dialog, select the data you want to restore and click '''Next'''.</translate></li>
<li><translate><!--T:127--> In the ''Select Files'' dialog, select the data you want to restore and click '''Next'''.</translate></li>
Line 68: Line 54:
<li><translate><!--T:31--> 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.</translate><br />
<li><translate><!--T:31--> 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.</translate><br />
<translate><!--T:32--> 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:</translate></li>
<translate><!--T:32--> 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:</translate></li>
<translate><!--T:33--> sbc -r -a recover -v 3 sbcmsql:<server_name>/<instance>/<DB_name></translate>
<pre> sbc -r -a recover -v 3 sbcmsql:<server_name>/<instance>/<DB_name></pre>
</ul></ul>
</ul></ul>
<translate><!--T:34--> [[Image:MS_SQL_restore_target_Beefalo_V2.jpg|800px|link=]]</translate>
<translate><!--T:34--> [[Image:MS_SQL_restore_target_Apollon.jpg|700px|link=]]</translate>
<br clear=all>
<br clear=all>
<li><translate><!--T:35--> 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'''.</translate></li></ol>
<li><translate><!--T:35--> 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'''.</translate></li></ol>
{{<translate><!--T:36--> note</translate>|<translate><!--T:37--> A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].</translate>}}
 
{{note|<translate><!--T:37--> A restore task can be scheduled like any other task. If you want to add a restore task to the schedule, see [[Special:MyLanguage/Scheduling_Restore|Scheduling Restore]].</translate>}}
 
<translate><!--T:38-->
<translate><!--T:38-->
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.
You can monitor the restore progress using the [[Special:MyLanguage/SEP_sesam_Web_UI|''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 [[Special:MyLanguage/5_0_0:SEP_sesam_Web_UI#restores|SEP sesam Web UI]] or [[Special:MyLanguage/4_4_3_Beefalo:Restores_by_State|Restores by State in the GUI]].</translate>


<!--T:133-->
=== {{anchor|new_location}}<translate><!--T:39-->
As of [[SEP_sesam_Release_Versions|4.4.3 ''Beefalo V2'']], you can also monitor the restore progress using the Web UI. For details, see [[Special:MyLanguage/4_4_3_Beefalo:SEP_sesam_Web_UI|SEP sesam Web UI]].
Restoring a database to a new location by using GUI ''relocation'' or ''move'' option ===  
 
=== {{anchor|new_location}}Restoring a database to a new location by using GUI relocation or ''move'' option === <!--T:39-->


<!--T:40-->
<!--T:40-->
Line 86: Line 72:


<!--T:41-->
<!--T:41-->
[[Special:MyLanguage/4_4_3:MS_SQL_Restore#relocation|> GUI ''relocation'' (≥ 4.4.3.45 ''Tigon V2'')]]
[[#relocation|> GUI ''relocation'' (≥ 4.4.3.45)]]


<!--T:42-->
<!--T:42-->
[[Special:MyLanguage/4_4_3:MS_SQL_Restore#move|> ''move'' option (≤ 4.4.3.42 ''Tigon'')]]</translate>
[[#move|> ''move'' option (≤ 4.4.3.42)]]</translate>
{{<translate><!--T:43--> Note</translate>|<translate><!--T:44--> 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!</translate>}}
{{Note|<translate><!--T:44--> 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!</translate>}}


<translate>===={{anchor|relocation}}By using GUI restore wizard ''relocation'' (≥ 4.4.3.45 ''Tigon V2'')==== <!--T:45-->
===={{anchor|relocation}}<translate><!--T:45-->
By using GUI restore wizard ''relocation''====  


<!--T:46-->
<!--T:46-->
As of [[SEP_sesam_Release_Versions|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.</translate>
Use the restore wizard to select the MS SQL database logical file name and configure relocated physical file name.</translate>
<ol><li><translate><!--T:47--> From the SEP sesam GUI menu bar, select '''Activities''' -> '''Restore'''. The ''New Restore Task'' window opens.</translate></li>
<ol><li><translate><!--T:47--> From the SEP sesam GUI menu bar, select '''Activities''' -> '''Restore'''. The ''New Restore Task'' window opens.</translate></li>
<li><translate><!--T:48--> Select the database you want to restore and then select the desired version.</translate></li>
<li><translate><!--T:48--> Select the database you want to restore and then select the desired version.</translate></li>
<li><translate><!--T:49--> 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.</translate></li>
<li><translate><!--T:49--> 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.</translate></li>
{{<translate><!--T:129--> note</translate>|<translate><!--T:130--> As of v. [[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3. ''Beefalo'']], the ''Expert Options'' button for specifying advanced restore options is only available in the [[Special:MyLanguage/SEP_sesam_Glossary#UI_mode|''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 [[Special:MyLanguage/4_4_3_Beefalo:SEP_sesam_GUI#UI_mode|Selecting UI mode]].</translate>}}
 
{{note|<translate><!--T:130--> The ''Expert Options'' button for specifying advanced restore options is available only in [[Special:MyLanguage/SEP_sesam_Glossary#UI_mode|''advanced'' UI mode]] (formerly ''expert'' GUI mode). To use ''Expert Options'', make sure your UI mode is set to ''advanced''. For details, see [[Special:MyLanguage/4_4_3_Beefalo:SEP_sesam_GUI#UI_mode|Selecting UI mode]].</translate>}}
<li><translate><!--T:50--> In the new ''Restore: Expert Options'' window, switch to the '''Relocation''' tab and select '''With relocation''' option.</translate> <br /><translate><!--T:51--> In the field ''Will be restored directly in the following target path'', the original path of the physical file is listed.</translate><br />  
<li><translate><!--T:50--> In the new ''Restore: Expert Options'' window, switch to the '''Relocation''' tab and select '''With relocation''' option.</translate> <br /><translate><!--T:51--> In the field ''Will be restored directly in the following target path'', the original path of the physical file is listed.</translate><br />  
<translate><!--T:134--> 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.</translate></li>
<translate><!--T:134--> 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.</translate></li>
{{<translate><!--T:135--> Note</translate>|<translate><!--T:136--> 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.</translate>}}
{{Note|<translate><!--T:136--> 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.</translate>}}
<translate><!--T:54--> [[image:MS_SQL_restore_relocation_Beefalo_V2.jpg|700px|link=]]</translate>
<translate><!--T:54--> [[image:MS_SQL_restore_relocation_Apollon.jpg|600px|link=]]</translate>
<br clear=all>
<br clear=all>
<translate><!--T:53--> Click '''OK''' to set a new path for the database, and then click '''Next'''.</translate>
<translate><!--T:53--> Click '''OK''' to set a new path for the database, and then click '''Next'''.</translate>
<li><translate><!--T:55--> To start your database restore immediately, click '''Start'''. Otherwise, save your restore task.</translate></li></ol>
<li><translate><!--T:55--> To start your database restore immediately, click '''Start'''. Otherwise, save your restore task.</translate></li></ol>


{{<translate><!--T:56--> Warning</translate>|
==== {{anchor|move}}<translate><!--T:58-->
<translate><!--T:57--> Pay special attention if you are using SEP sesam version [[Special:MyLanguage/SEP_sesam_Release_Versions|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_v.2#Known_issues_and_limitations|Release Notes 4.4.3 Tigon V2]] for details.</translate>}}
By using the ''move'' option (older versions: ≤ 4.4.3.42)====  
 
<translate>==== {{anchor|move}}By using the ''move'' option (≤ 4.4.3.42 ''Tigon'')==== <!--T:58-->


<!--T:59-->
<!--T:59-->
For versions [[SEP_sesam_Release_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.  
For older versions ([[SEP_sesam_Release_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.  


<!--T:60-->
<!--T:60-->
Line 126: Line 112:


<!--T:64-->
<!--T:64-->
Use the ''move'' option as follows:
Use the ''move'' option as follows:</translate>


  <!--T:65-->
  -a move={<original_logical_name>}:"<new_file_name_mdf>" -a move={<original_logical_name_ldf>}:"<new_file_name_ldf>"
-a move={<original_logical_name>}:"<new_file_name_mdf>" -a move={<original_logical_name_ldf>}:"<new_file_name_ldf>"


<!--T:66-->
<translate><!--T:66-->
Example</translate>
Example</translate>
  osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2  
  osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2  
Line 139: Line 124:
  sesam_db2_log  D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf  ONLINE
  sesam_db2_log  D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf  ONLINE


{{<translate><!--T:67--> tip</translate>|<translate><!--T:68--> You can use the restore wizard and enter the ''move'' option <nowiki>''-a move=.:.''</nowiki> as text (without changing lines) by using '''Expert Options''' -> tab '''Option''' -> ''Restore options'' field.</translate>}}  
{{tip|<translate><!--T:68--> You can use the restore wizard and enter the ''move'' option <nowiki>''-a move=.:.''</nowiki> as text (without changing lines) by using '''Expert Options''' -> tab '''Option''' -> ''Restore options'' field.</translate>}}  


{{<translate><!--T:69--> note</translate>|<translate><!--T:70--> 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.</translate>}}
{{note|<translate><!--T:70--> 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.</translate>}}


<translate><!--T:71--> '''''Example 1''''': Restoring the backup of the database '''sesam_db''' to '''sesam2''' by using ''move''.</translate>
<translate><!--T:71--> '''''Example 1''''': Restoring the backup of the database '''sesam_db''' to '''sesam2''' by using ''move''.</translate>
Line 159: Line 144:


<translate><!--T:73-->
<translate><!--T:73-->
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.  
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. </translate>


=== {{anchor|file_name}}Restoring a database to a new location by changing the database name  === <!--T:74-->
=== {{anchor|file_name}}<translate><!--T:74-->
Restoring a database to a new location by changing the database name  ===  


<!--T:75-->
<!--T:75-->
Line 176: Line 162:
# <translate><!--T:84--> Once the restore is completed successfully, set the ''RestoreDB'' database online.</translate>
# <translate><!--T:84--> Once the restore is completed successfully, set the ''RestoreDB'' database online.</translate>


<translate>=={{anchor|disaster}}Disaster recovery== <!--T:85-->
=={{anchor|disaster}}<translate><!--T:85-->
Disaster recovery==


<!--T:86-->
<!--T:86-->
Line 250: Line 237:
  osql -E -S w2003enterprise -Q "select name, filename from sysdatabases" -d master
  osql -E -S w2003enterprise -Q "select name, filename from sysdatabases" -d master
</div></div>
</div></div>
 
<translate><div class="noprint">
<noinclude>{{Copyright}}</noinclude>
==See also== <!--T:120-->
[[Special:MyLanguage/4_4_3:MS_SQL_Backup|MS SQL Backup]]</div></translate>

Latest revision as of 11:40, 9 June 2023

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.

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.
  • If you have an encrypted backup of multiple MS SQL databases, it is not possible to restore a single database, only all databases together. If you want to use encryption, you should create a separate job for each database to enable a smooth restore.

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.

  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 Apollon.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 Apollon.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 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:

> GUI relocation (≥ 4.4.3.45)

> move option (≤ 4.4.3.42)

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

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 and then select the desired version.
  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
    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.
  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 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.
  6. Information sign.png 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.

    MS SQL restore relocation Apollon.jpg
    Click OK to set a new path for the database, and then click Next.

  7. To start your database restore immediately, click Start. Otherwise, save your restore task.

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