Source:MS SQL Backup: Difference between revisions

From SEPsesam
(Added collapse lists for the commands.)
(Minor correction.)
(15 intermediate revisions by 2 users not shown)
Line 1: Line 1:
<div class="noprint">{{draft}}
<translate><!--T:1-->
<div class="noprint"><languages />
{{Copyright SEP AG‎|en}}
{{Copyright SEP AG‎|en}}
{{Navigation_latest|release=[[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3/4.4.3 ''Grolar'']]|link=[[Special:MyLanguage/MS_SQL|MS SQL]]}}</div><br />
{{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 />


==Overview==
<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>Additional resources</b></center>
<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;"
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | [[File:SEP_next.png|45px|link=Special:MyLanguage/4_4_3:MS_SQL_Restore]]
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:4--> [[File:SEP_next.png|45px|link=Special:MyLanguage/4_4_3:MS_SQL_Restore]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" | See also: [[Special:MyLanguage/4_4_3:MS_SQL_Restore|MS SQL Restore]]
| 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_Restore|MS SQL Restore]] – [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]]</translate>
|}
|}


{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | [[File:SEP Tip.png|45px|link=Special:MyLanguage/FAQ#VSS_functionality|FAQ]]
| rowspan="2" style="padding:0px 10px 0px;" | <translate><!--T:6--> [[File:SEP Tip.png|45px|link=Special:MyLanguage/FAQ#VSS_functionality|FAQ#VSS_functionality]]</translate>
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" |
| style="padding:0px 40px 0px 10px; color: grey; font-size: 90%; text-align:left;" |
Check [[Special:MyLanguage/FAQ#VSS_functionality|FAQ]] to find the answers to most common questions.
<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;"
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | [[File:SEP Troubleshooting.png|45px|link=Troubleshooting_Guide#MS_SQL]]
| 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;" | Problems? Check the [[Special:MyLanguage/Troubleshooting_Guide#MS_SQL|MS SQL troubleshooting]].
| 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;"
{|style="margin: auto; margin-bottom:1em; width:100%; border:0px solid grey;"
| rowspan="2" style="padding:0px 10px 0px;" | [[File:icon_archived_docs.png|45px|link=IBM_DB2]]
| 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;" | If you are using an older SEP sesam version, refer to [[Special:MyLanguage/MS_SQL|MS SQL archive]].
| 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>
|}</div></noinclude>
<translate><!--T:12-->
SEP sesam database module for MS SQL is a part of a client package and enables backing up one or more MS SQL databases; supported backup levels depend on the selected recovery model, as described in section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]].   
SEP sesam database module for MS SQL is a part of a client package and enables backing up one or more MS SQL databases; supported backup levels depend on the selected recovery model, as described in section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]].   


<!--T:13-->
The source for a MS SQL Server backup must contain the SQL Server database name. If the database belongs to a SQL Server instance, then the server and the instance must be specified explicitly: {{path|<server_name>/<instance>/<database>}}. The names of the instances and databases may be retrieved with ''SQL Server Management Studio''. For details, see [[Special:MyLanguage/4_4_3:MS_SQL_Backup#list|Listing SQL Servers from the command line]].   
The source for a MS SQL Server backup must contain the SQL Server database name. If the database belongs to a SQL Server instance, then the server and the instance must be specified explicitly: {{path|<server_name>/<instance>/<database>}}. The names of the instances and databases may be retrieved with ''SQL Server Management Studio''. For details, see [[Special:MyLanguage/4_4_3:MS_SQL_Backup#list|Listing SQL Servers from the command line]].   


===Key features===
==={{anchor|key_features}}Key features=== <!--T:14-->
SEP sesam supports the following backup levels of SQL Server:
*'''Full backup''': A full database backup backs up the whole database stored on MS SQL server, including the transaction log. Full backups provide a duplicate of the database and enable database restore and recovery. Backups are performed at the database instance level.
*'''Differential backup''': Only the data that has changed since the last database backup is backed up. The full backup upon which a differential backup is based is the base of the differential. Note that restoring a differential backup requires its base to be restored first.
*'''Transaction log backup (INCR)''': SEP sesam uses [[Special:Mylanguage/SEP_sesam_Glossary#INCR|INCR]] backup to back up the transaction log. This [[Special:MyLanguage/SEP_sesam_Glossary#backup_level|backup level]] is relevant only for SQL Server databases that are using the ''full'' or ''bulk-logged'' recovery model. It requires at least one full backup to be performed before creating any log backups. Incremental (log) backups contain <tt>.TRN</tt> files and provide all transactions that have changed since the last FULL, DIFF or INCR.
*'''Cluster''': Microsoft SQL environment with '''Always On Failover Cluster''' (active/passive).
{{tip|Plan your SQL Server backups carefully. SEP recommends using the [[Special:Mylanguage/4_4_3:MS_SQL_Backup#recovery_module|''full recovery model'']]. As for the backup strategy, a typical backup schema may include weekly full database backups, daily differential backups and frequent intervals of the transaction log (INCR) backup, e.g., every 15 minutes. Note that the optimal interval for each backup level depends on the importance of the data, database size, frequency of changes, server workload, etc.}}


==Requirements==
<!--T:15-->
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:
SEP sesam supports the following backup levels of SQL Server:</translate>
*Check the [[SEP_sesam_OS_and_Database_Support_Matrix#Microsoft_SQL_Server|support matrix]] for the list of supported MS SQL versions.
*<translate><!--T:16--> '''Full backup''': A full database backup backs up the whole database stored on MS SQL server, including the transaction log. Full backups provide a duplicate of the database and enable database restore and recovery. Backups are performed at the database instance level.</translate>
*''SQL Server 2014'': On Windows Server 2008 Service Pack 2 must be installed.
*<translate><!--T:17--> '''Differential backup''': Only the data that has changed since the last database backup is backed up. The full backup upon which a differential backup is based is the base of the differential. Note that restoring a differential backup requires its base to be restored first.</translate>
* SEP sesam Server v. ≥ 4.4.3. Check [[Special:MyLanguage/Hardware_requirements|Hardware requirements]] for [[Special:MyLanguage/SEP_sesam_Glossary#SEP_sesam_Server|SEP sesam Server]].
*<translate><!--T:18--> '''Transaction log backup (INCR)''': SEP sesam uses [[Special:Mylanguage/SEP_sesam_Glossary#INCR|INCR]] backup to back up the transaction log. This [[Special:MyLanguage/SEP_sesam_Glossary#backup_level|backup level]] is relevant only for SQL Server databases that are using the ''full'' or ''bulk-logged'' recovery model. It requires at least one full backup to be performed before creating any log backups. Incremental (log) backups contain <tt>.TRN</tt> files and provide all transactions that have changed since the last FULL, DIFF or INCR.</translate>
* [https://download.sep.de/ Download the SEP sesam Client package] on the MS SQL server (MS SQL database module is already included in the client package).  
*<translate><!--T:19--> '''Cluster''': Microsoft SQL Server on '''Windows Failover Cluster''' (active/passive) and '''AlwaysOn Availability Groups'''.</translate>
*A user account with sufficient privileges to connect to all SQL databases. For details, see section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#user_account|Specifying the user account for the SEP sesam client service]].
{{<translate><!--T:20--> tip</translate>|<translate><!--T:21--> Plan your SQL Server backups carefully. SEP recommends using the [[Special:Mylanguage/4_4_3:MS_SQL_Backup#recovery_module|''full recovery model'']]. As for the backup strategy, a typical backup schema may include weekly full database backups, daily differential backups and frequent intervals of the transaction log (INCR) backup, e.g., every 15 minutes. Note that the optimal interval for each backup level depends on the importance of the data, database size, frequency of changes, server workload, etc.</translate>}}
*SEP sesam MS SQL backup module requires a license. For details, see [[Special:MyLanguage/List_of_Licenses|List of Licenses]].


==Restrictions==
<translate>==Requirements== <!--T:22-->
* In case of ''Microsoft SQL Server 2012/2014/2016'' replication scenario, only the '''''Publisher database''''' is supported for backups. Backup of replicated ''Distributor'' or ''Subscriber database'' is not supported.
* A distributed Microsoft SQL environment with ''Always On availability groups'' is currently not supported.


== {{anchor|configuration}}MS SQL configuration==
<!--T:23-->
==={{anchor|user_account}}Specifying the user account for the SEP sesam Client service===
To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:</translate>
<ol><li>On the MS SQL server, configure the SEP sesam Client service with a user account that has appropriate permissions (backup operator) to connect to all SQL databases; typically, this is a user with '''domain administrator privileges'''.</li>
*<translate><!--T:24--> Check the [[SEP_sesam_OS_and_Database_Support_Matrix#Microsoft_SQL_Server|support matrix]] for the list of supported MS SQL versions.</translate>
{{note|MS SQL backup is not supported if the SEP sesam Client service is running under the ''Local System'' account!}}
*<translate><!--T:25--> ''SQL Server 2014'': On Windows Server 2008 Service Pack 2 must be installed.</translate>
Open the Windows service management console on your MS SQL server, edit the properties of the SEP sesam service, then switch to '''Log on''' tab and use valid credentials. Ensure that the specified user has appropriate permissions for backing up and restoring SQL databases.<br />
*<translate><!--T:26--> SEP sesam Server v. ≥ 4.4.3. Check [[Special:MyLanguage/SEP_sesam_Requirements#hardware|Hardware requirements]] for [[Special:MyLanguage/SEP_sesam_Glossary#SEP_sesam_Server|SEP sesam Server]].</translate>
[[File:System account sql.PNG|link=]]
*<translate><!--T:27--> [https://download.sep.de/ Download the SEP sesam Client package] on the MS SQL server (MS SQL database module is already included in the client package).</translate>
*<translate><!--T:28--> A user account with sufficient privileges to connect to all SQL databases. For details, see section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#user_account|Specifying the user account for the SEP sesam client service]].</translate>
*<translate><!--T:29-->
SEP sesam MS SQL backup module requires a license. For details, see [[Special:MyLanguage/List_of_Licenses|List of Licenses]].
 
==Restrictions== <!--T:30--></translate>
* <translate><!--T:31--> In case of ''Microsoft SQL Server 2012/2014/2016'' replication scenario, only the '''''Publisher database''''' is supported for backups. Backup of replicated ''Distributor'' or ''Subscriber database'' is not supported.</translate>
* <translate><!--T:32-->
A distributed Microsoft SQL environment with ''AlwaysOn Availability Groups'' is currently only supported by SEP sesam with the assistance of SEP sesam support.
 
== {{anchor|configuration}}MS SQL configuration== <!--T:33-->
 
==={{anchor|user_account}}Specifying the user account for the SEP sesam Client service=== <!--T:34--></translate>
 
<ol><li><translate><!--T:35--> On the MS SQL server, configure the SEP sesam Client service with a user account that has appropriate permissions (backup operator) to connect to all SQL databases; typically, this is a user with '''domain administrator privileges'''.</translate></li>
{{<translate><!--T:36--> note</translate>|<translate><!--T:37--> MS SQL backup is not supported if the SEP sesam Client service is running under the ''Local System'' account!</translate>}}
<translate><!--T:38--> Open the Windows service management console on your MS SQL server, edit the properties of the SEP sesam service, then switch to '''Log on''' tab and use valid credentials. Ensure that the specified user has appropriate permissions for backing up and restoring SQL databases.</translate><br />
<translate><!--T:39--> [[File:System_account_sql.PNG|link=]]</translate>
<br clear=all>
<br clear=all>
<li>After this step restart the SEP sesam daemon. For details, see [[Special:MyLanguage/How_to_Start_and_Stop_SEP_sesam|How to Start and Stop SEP sesam]].</li></ol>
<li><translate><!--T:40--> After this step restart the SEP sesam daemon. For details, see [[Special:MyLanguage/How_to_Start_and_Stop_SEP_sesam|How to Start and Stop SEP sesam]].</translate></li></ol>


==={{anchor|recovery_model}}Selecting a recovery model for your MS SQL database===
<translate>==={{anchor|recovery_model}}Selecting a recovery model for your MS SQL database=== <!--T:41-->
MS SQL server provides different recovery models. Before you configure SEP sesam backup for your MS SQL databases, you have to select the relevant recovery model in your ''SQL Server Management Studio''. Recovery models are designed to control transaction log maintenance and will affect how backups are performed and what kind of recovery is possible:<br />
 
<!--T:42-->
MS SQL server provides different recovery models. Before you configure SEP sesam backup for your MS SQL databases, you have to select the relevant recovery model in your ''SQL Server Management Studio''. Recovery models are designed to control transaction log maintenance and will affect how backups are performed and what kind of recovery is possible:</translate><br />
<translate><!--T:43-->
Right click on the ''database name'', select '''Properties''', select the '''Options''' tab and then select the recovery model from the drop-down list (the difference between the models is explained below). Click '''OK''' to save your selection.
Right click on the ''database name'', select '''Properties''', select the '''Options''' tab and then select the recovery model from the drop-down list (the difference between the models is explained below). Click '''OK''' to save your selection.


The following three recovery models are available: ''simple'', ''full'', and ''bulk-logged''. Typically, a database uses the full recovery model or simple recovery model. For detailed explanation, refer to Microsoft article [https://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx Recovery Model Overview].  
<!--T:44-->
;Simple recovery model: Provides a simple backup that can be used to recover the entire database only to the most recent backup, because the transaction log is not backed up. With this recovery model all transactions since the last backup are lost, thus exposing you to any failures since the last backup completed. The following note recaps the Microsoft article [https://technet.microsoft.com/en-us/library/ms191164%28v=sql.105%29.aspx Backup Under the Simple Recovery Model] about the data loss exposure:
The following three recovery models are available: ''simple'', ''full'', and ''bulk-logged''. Typically, a database uses the full recovery model or simple recovery model. For detailed explanation, refer to Microsoft article [https://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx Recovery Model Overview].</translate>
{{note|The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, you should use the full recovery model.}}
;<translate><!--T:45--> Simple recovery model: Provides a simple backup that can be used to recover the entire database only to the most recent backup, because the transaction log is not backed up. With this recovery model all transactions since the last backup are lost, thus exposing you to any failures since the last backup completed. The following note recaps the Microsoft article [https://technet.microsoft.com/en-us/library/ms191164%28v=sql.105%29.aspx Backup Under the Simple Recovery Model] about the data loss exposure:</translate>
;Full recovery model: The most complete recovery model that allows a database to be recovered to any point in time as long as all backup files are usable. Note that transaction log backups are required. SEP sesam provides the transaction log backup with [[Special:Mylanguage/SEP_sesam_Glossary#INCR|INCR]] backup level. Therefore, the full recovery model must be used when backing up a database with SEP sesam FULL/DIFF/INCR (transaction log backup) backup level.
{{<translate><!--T:46--> note</translate>|<translate><!--T:47--> The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, you should use the full recovery model.</translate>}}
;Bulk logged model: An adjunct of the full recovery model which does not fully log certain bulk operations, such as BULK INSERT, CREATE INDEX, SELECT INTO, etc. in the transaction log, thus reducing log space consumption. Note that bulk-logged recovery model does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.  
;{{anchor|full}}<translate><!--T:48--> Full recovery model: The most complete recovery model that allows a database to be recovered to any point in time as long as all backup files are usable. Note that transaction log backups are required. SEP sesam provides the transaction log backup with [[Special:Mylanguage/SEP_sesam_Glossary#INCR|INCR]] backup level. Therefore, the full recovery model must be used when backing up a database with SEP sesam FULL/DIFF/INCR (transaction log backup) backup level.</translate>
;<translate><!--T:49--> Bulk logged model: An adjunct of the full recovery model which does not fully log certain bulk operations, such as BULK INSERT, CREATE INDEX, SELECT INTO, etc. in the transaction log, thus reducing log space consumption. Note that bulk-logged recovery model does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.</translate>


{{note|Depending on the selected recovery model, you have to select the appropriate SEP sesam [[Special:Mylanguage/SEP_sesam_Glossary#backup_level|backup level]]. For example, if you want to utilize the transaction log backups ([[Special:Mylanguage/SEP_sesam_Glossary#INCR|SEP sesam INCR backup]]), you have to use the full recovery model, otherwise an error is issued, as described in the following ''MS SQL recovery model types and backup levels'' section.}}
{{<translate><!--T:50--> note</translate>|<translate><!--T:51--> Depending on the selected recovery model, you have to select the appropriate SEP sesam [[Special:Mylanguage/SEP_sesam_Glossary#backup_level|backup level]]. For example, if you want to utilize the transaction log backups ([[Special:Mylanguage/SEP_sesam_Glossary#INCR|SEP sesam INCR backup]]), you have to use the full recovery model, otherwise an error is issued, as described in the following ''MS SQL recovery model types and backup levels'' section.</translate>}}


==== MS SQL recovery model types and backup levels ====
<translate>==== MS SQL recovery model types and backup levels ==== <!--T:52--></translate>
{| border="2" cellpadding="4" cellspacing="0" style="width:90%; margin: 1em 1em 1em 0; background: #fcfcfc; border: 1px #aaa solid; border-collapse: collapse;"
{| border="2" cellpadding="4" cellspacing="0" style="width:90%; margin: 1em 1em 1em 0; background: #fcfcfc; border: 1px #aaa solid; border-collapse: collapse;"
|- style="background:#FFCC01; color:#002F55"
|- style="background:#FFCC01; color:#002F55"
! scope="col" style="width: 150px;" | Recovery model
! scope="col" style="width: 150px;" | <translate><!--T:53--> Recovery model</translate>
! scope="col" style="width: 300px;" | Description
! scope="col" style="width: 300px;" | <translate><!--T:54--> Description</translate>
! scope="col" style="width: 250px;" | Supported backup levels
! scope="col" style="width: 250px;" | <translate><!--T:55--> Supported backup levels</translate>
! scope="col" style="width: 400px;"| Recovery options
! scope="col" style="width: 400px;"| <translate><!--T:56--> Recovery options</translate>
|-
|-
  |'''Simple''' ||The transaction log is not backed up. ||FULL, COPY, DIFF || Allows the recovery of the entire database only to the most recent backup.
  |<translate><!--T:57--> '''Simple'''</translate> ||<translate><!--T:58--> The transaction log is not backed up.</translate> ||FULL, COPY, DIFF || <translate><!--T:59--> Allows the recovery of the entire database only to the most recent backup.</translate>
  |-
  |-
  |'''Full'''||Transaction log backups are required. ||FULL, COPY, DIFF, INCR ||Allows a database to be recovered to any point in time as long as all backup files are usable.
  |<translate><!--T:60--> '''Full'''</translate>||<translate><!--T:61--> Transaction log backups are required.</translate> ||FULL, COPY, DIFF, INCR ||<translate><!--T:62--> Allows a database to be recovered to any point in time as long as all backup files are usable.</translate>
  |-
  |-
  |'''Bulk logged'''||Transaction log backups are required.||FULL, COPY, DIFF, INCR||Does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.  
  |<translate><!--T:63--> '''Bulk logged'''</translate>||<translate><!--T:64--> Transaction log backups are required.</translate>||FULL, COPY, DIFF, INCR||<translate><!--T:65--> Does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.</translate>
  |-
  |-
|}
|}
Depending on SEP sesam backup level (COPY or FULL/DIFF/INCR), the SQL database recovery model and specified backup source, the backup will issue warnings or fail if a transaction log backup (INCR) is performed on database which does not support log backups (is configured with simple recovery model). MS SQL backup success status can be one of the following:
<translate><!--T:66--> Depending on SEP sesam backup level (COPY or FULL/DIFF/INCR), the SQL database recovery model and specified backup source, the backup will issue warnings or fail if a transaction log backup (INCR) is performed on database which does not support log backups (is configured with simple recovery model). MS SQL backup success status can be one of the following:</translate>
<ul><li>COPY and DIFF backups can be used for any SQL database recovery model. These backups should finish successfully with simple, full or bulk-logged model.</li>
<ul><li><translate><!--T:67--> COPY and DIFF backups can be used for any SQL database recovery model. These backups should finish successfully with simple, full or bulk-logged model.</translate></li>


<li>When a database uses ''simple recovery model'' and in the backup task this ''particular database is set as a source'', e.g., <tt>my_database</tt>, then COPY/FULL/DIFF backups will finish successfully but INCR will fail with message:</li>
<li><translate><!--T:68--> When a database uses ''simple recovery model'' and in the backup task this ''particular database is set as a source'', e.g., <tt>my_database</tt>, then COPY/FULL/DIFF backups will finish successfully but INCR will fail with message:</translate></li>
  Error: DB Module: [Incremental backup for database with simple recovery model]
  <translate><!--T:69--> Error: DB Module: [Incremental backup for database with simple recovery model]</translate>


<li>When a database uses ''simple recovery model'' and in the backup task the ''source is set to multiple databases (e.g., with specified instance or as 'all')'', which means that this database is backed up in one session together with other databases, then only COPY and DIFF backups are successful. FULL/INCR backup will end with warning <tt>database not logged</tt> for the respective database:</li>
<li><translate><!--T:70--> When a database uses ''simple recovery model'' and in the backup task the ''source is set to multiple databases (e.g., with specified instance or as 'all')'', which means that this database is backed up in one session together with other databases, then only COPY and DIFF backups are successful. FULL/INCR backup will end with warning <tt>database not logged</tt> for the respective database:</translate></li>
  Warning: Item [''<database_name>''] is not logged. Recovering may fail.
  <translate><!--T:71--> Warning: Item [''<database_name>''] is not logged. Recovering may fail.</translate>


<li>As of [[SEP_sesam_Release_Versions|SEP sesam v. 4.4.3 ''Tigon V2'']], there are some exceptions when logging the backup status for the system databases ''master'', ''model'' and ''msdb''. If these databases are configured with ''simple recovery model'' and a FULL/INCR backup is performed, the missing transaction log backup for these system DBs is ignored and no warnings are issued during the backup.</li>
<li><translate><!--T:72--> As of [[SEP_sesam_Release_Versions|SEP sesam v. 4.4.3 ''Tigon V2'']], there are some exceptions when logging the backup status for the system databases ''master'', ''model'' and ''msdb''. If these databases are configured with ''simple recovery model'' and a FULL/INCR backup is performed, the missing transaction log backup for these system DBs is ignored and no warnings are issued during the backup.</translate></li>
This new ''no-warning behavior'' is based on recommended backup strategy for system databases: typically, ''master'' and ''msdb'' databases are using simple recovery mode (although the recovery mode can be set to FULL for ''master'' DB, transaction log backup cannot be performed); the ''model'' database sets the default recovery model of new databases and should have the recovery mode set to FULL. For details, see Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server Back Up and Restore of System Databases (SQL Server)].</ul>
<translate><!--T:73--> This new ''no-warning behavior'' is based on recommended backup strategy for system databases: typically, ''master'' and ''msdb'' databases are using simple recovery mode (although the recovery mode can be set to FULL for ''master'' DB, transaction log backup cannot be performed); the ''model'' database sets the default recovery model of new databases and should have the recovery mode set to FULL. For details, see Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server Back Up and Restore of System Databases (SQL Server)].</translate></ul>
{{tip|If you have a mixture of databases on the same SQL Server, i.e., some with simple and other with full recovery mode, create separate backup taks for DBs with simple and DBs with full model.}}
{{<translate><!--T:74--> tip</translate>|<translate><!--T:75--> If you have a mixture of databases on the same SQL Server, i.e., some with simple and other with full recovery mode, create separate backup tasks for DBs with simple and DBs with full model.</translate>}}


==== Transaction log truncation ====
<translate>==== Transaction log truncation ==== <!--T:76-->
 
<!--T:77-->
SQL Server database transaction log records all transactions and related database modifications. Transaction log backups are essential if you want to recover the database to a specific point in time or to the point of failure. Point-in-time restore is only possible with SQL database ''full recovery model'' (see section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]], which requires scheduled FULL (database backup) as well as INCR (log) backups to get the unbroken log chain that enables the database restore to any point in time.
SQL Server database transaction log records all transactions and related database modifications. Transaction log backups are essential if you want to recover the database to a specific point in time or to the point of failure. Point-in-time restore is only possible with SQL database ''full recovery model'' (see section [[Special:MyLanguage/4_4_3:MS_SQL_Backup#recovery_model|Selecting a recovery model for your MS SQL database]], which requires scheduled FULL (database backup) as well as INCR (log) backups to get the unbroken log chain that enables the database restore to any point in time.


<!--T:78-->
After the transaction log (INCR) backup, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portions (these are completed transactions that are no longer required for recovery) of the transaction log thus preventing the transaction log to grow and fill up. Note that it cannot be truncated until all of its records are captured in a log (INCR) backup! Therefore, under the SQL database ''full recovery model'' the log files will not be truncated at all without the INCR backup.
After the transaction log (INCR) backup, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portions (these are completed transactions that are no longer required for recovery) of the transaction log thus preventing the transaction log to grow and fill up. Note that it cannot be truncated until all of its records are captured in a log (INCR) backup! Therefore, under the SQL database ''full recovery model'' the log files will not be truncated at all without the INCR backup.


As specified in the Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server The Transaction Log (SQL Server)], log truncation typically occurs automatically after the following events:
<!--T:79-->
*When ''simple recovery model'' is used, after a checkpoint.
As specified in the Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server The Transaction Log (SQL Server)], log truncation typically occurs automatically after the following events:</translate>
*When ''full recovery model'' or ''bulk-logged recovery model'' is used, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a ''copy-only'' log backup).
*<translate><!--T:80--> When ''simple recovery model'' is used, after a checkpoint.</translate>
{{note|
*<translate><!--T:81--> When ''full recovery model'' or ''bulk-logged recovery model'' is used, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a ''copy-only'' log backup).</translate>
*Some factors, such as a long-running transaction, can delay log truncation, therefore you should monitor log size regularly. You should not truncate the transaction log manually as this will break the backup chain.
{{<translate><!--T:82--> note</translate>|
*Transaction log (INCR) backup is based on FULL backup, so make sure that at least one successful full backup exists before creating any log backups.}} <!--The incremental backup task will execute a log backup without option 'WITH NO_TRUNCATE' or option 'WITH COPY_ONLY'.-->
*<translate><!--T:83--> Some factors, such as a long-running transaction, can delay log truncation, therefore you should monitor log size regularly. You should not truncate the transaction log manually as this will break the backup chain.</translate>
*<translate><!--T:84--> Transaction log (INCR) backup is based on FULL backup, so make sure that at least one successful full backup exists before creating any log backups.</translate>}} <!--The incremental backup task will execute a log backup without option 'WITH NO_TRUNCATE' or option 'WITH COPY_ONLY'.-->


<translate><!--T:85-->
For details on managing the transaction logs, see Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server The Transaction Log (SQL Server)].
For details on managing the transaction logs, see Microsoft article [https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server The Transaction Log (SQL Server)].


==={{anchor|add_client}}Adding the MS SQL client to SEP sesam environment ===
==={{anchor|add_client}}Adding the MS SQL client to SEP sesam environment === <!--T:86-->


Once you have [https://download.sep.de/ downloaded the SEP sesam package], configure your MS SQL client by adding it to the SEP sesam environment: '''Main selection''' -> '''Components''' -> '''Topology''' -> '''New client''' -> add your MS SQL client. For details, see [[Special:MyLanguage/Configuring_Clients|Configuring Clients]].  
<!--T:87-->
{{note|Before proceeding, you should validate that you can backup to SEP sesam. Run a test ''Path'' backup manually to ensure that the backups work. For details on typical backup configuration, see [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]].}}
Once you have [https://download.sep.de/ downloaded the SEP sesam package], configure your MS SQL client by adding it to the SEP sesam environment: '''Main selection''' -> '''Components''' -> '''Topology''' -> '''New Client''' -> add your MS SQL client. For details, see [[Special:MyLanguage/Configuring_Clients|Configuring Clients]].</translate>
{{<translate><!--T:88--> note</translate>|<translate><!--T:89--> Before proceeding, you should validate that you can backup to SEP sesam. Run a test ''Path'' backup manually to ensure that the backups work. For details on typical backup configuration, see [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]].</translate>}}


=={{anchor|database_bck}}Configuring MS SQL database backup ==
<translate>=={{anchor|database_bck}}Configuring MS SQL database backup == <!--T:90-->
To back up your SQL server data, you have to create a backup task (select the data to be backed up), then set up a backup schedule (specify when you want to back up your data), and link a backup event to it – in this 3rd step you select the appropriate SEP sesam [[Special:Mylanguage/SEP_sesam_Glossary#backup_level|backup level]] depending on the used recovery model of your SQL databases. For example, if you want to utilize the transaction log backups ([[Special:Mylanguage/SEP_sesam_Glossary#INCR|SEP sesam INCR backup]]), you have to use the ''full recovery model'', otherwise an error is issued. For details, see section [[Special:Mylanguage/4_4_3:MS_SQL_Backup#recovery_module|Selecting a recovery model for your MS SQL database]]. For general details on how to configure a backup and its prerequisites, see [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]].
 
{{tip|If you have a mixture of databases on the same MS SQL Server, i.e., some with ''simple'' and other with ''full recovery mode'', create separate backup tasks for DBs with simple and DBs with full model.}}
<!--T:91-->
Create a new backup task to backup all or only selected databases on an instance.
To back up your SQL server data, you have to create a backup task (select the data to be backed up), then set up a backup schedule (specify when you want to back up your data), and link a backup event to it – in this 3rd step you select the appropriate SEP sesam [[Special:Mylanguage/SEP_sesam_Glossary#backup_level|backup level]] depending on the used recovery model of your SQL databases. For example, if you want to utilize the transaction log backups ([[Special:Mylanguage/SEP_sesam_Glossary#INCR|SEP sesam INCR backup]]), you have to use the ''full recovery model'', otherwise an error is issued. For details, see section [[Special:Mylanguage/4_4_3:MS_SQL_Backup#recovery_module|Selecting a recovery model for your MS SQL database]]. For general details on how to configure a backup and its prerequisites, see [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]].</translate>
<ol><li>From '''Main Selection''' -> '''Tasks''' -> '''By clients''', select ''MS SQL'' client and click '''New backup task'''. The ''New backup task'' window opens.</li>
{{<translate><!--T:92--> tip</translate>|<translate><!--T:93--> If you have a mixture of databases on the same MS SQL Server, i.e., some with ''simple'' and other with ''full recovery mode'', create separate backup tasks for DBs with simple and DBs with full model.</translate>}}
<li>Click the '''Browse''' button (big arrow) and select either the single database or all MS SQL databases you want to back up. You can also enter the source manually, as follows:
<translate><!--T:94--> Create a new backup task to back up all or only selected databases on an instance.</translate>
<ul><li>''<hostname>/<instance>/<DB_name>'': Specify a backup of a specific database; the database name is case-sensitive.
<ol><li><translate><!--T:95--> From '''Main Selection''' -> '''Tasks''' -> '''By Clients''', select ''MS SQL'' client and click '''New Backup Task'''. The ''New Backup Task'' window opens.</translate></li>
<li>''all'': Specify a backup of all databases on an instance.</li></ul>
<li><translate><!--T:96--> Click the '''Browse''' button (big arrow) and select either the single database or all MS SQL databases you want to back up. You can also enter the source manually, as follows:</translate>
Note that if you select the source by browsing, the ''task type'' and ''task name'' are set automatically. If you enter the source manually, you have to enter the ''task name'' and select '''MS SQL Server''' as the ''task type''.  
<ul><li><translate><!--T:97--> ''<hostname>/<instance>/<DB_name>'': Specify a backup of a specific database; the database name is case-sensitive.</translate>
[[image:MS_SQL_bck.png|link=]]
<li><translate><!--T:98--> ''all'': Specify a backup of all databases on an instance.</translate></li></ul>
<translate><!--T:99--> Note that if you select the source by browsing, the ''task type'' and ''task name'' are set automatically. If you enter the source manually, you have to enter the ''task name'' and select '''MS SQL Server''' as the ''task type''.</translate><br />
<translate><!--T:100--> [[image:MS_SQL_bck_task_Beefalo_V2.jpg|link=]]</translate>
<br clear=all>
<br clear=all>
<li>You may want to exclude some databases on an instance from the backup. Use the ''Exclude List Editor'' to specify the exclusions by using regular expressions. Add the exclusion patterns one by one followed by a comma. You can also use a file to define a backup source to be excluded. For details on various exclude options, see [[Special:MyLanguage/Creating_Exclude_List|Creating Exclude List]]. </li>
<li><translate><!--T:101--> You may want to exclude some databases on an instance from the backup. Use the ''Exclude List Editor'' to specify the exclusions by using regular expressions. Add the exclusion patterns one by one followed by a comma. You can also use a file to define a backup source to be excluded. For details on various exclude options, see [[Special:MyLanguage/Creating_Exclude_List|Creating Exclude List]].</translate></li>
<li>Optionally, switch to the '''Options tab''' and enter additional options for backup in the '''Save options''' field, if required.<br />
<li><translate><!--T:102--> Optionally, switch to the '''Options''' tab and enter additional options for backup in the '''Backup options''' (previously ''Save options'') field, if required.</translate><br />
Click '''OK''' to create the task.</li>
<translate><!--T:103--> Click '''OK''' to create the task.</translate></li>
<li>If you want to start the newly created task immediately, right-click the name of the task and click '''Immediate Start'''. If you want to create a periodic backup, you have to create a schedule for your backup task: Click '''New schedule''' under '''Main Selection''' -> '''Scheduling''' -> '''Schedules''' and set up a schedule. For more details, see [[Special:MyLanguage/Creating_a_Schedule|Creating a Schedule]].</li>
<li><translate><!--T:104--> If you want to start the newly created task immediately, right-click the name of the task and click '''Immediate Start'''. If you want to create a periodic backup, you have to create a schedule for your backup task: Click '''New Schedule''' under '''Main Selection''' -> '''Scheduling''' -> '''Schedules''' and set up a schedule. For more details, see [[Special:MyLanguage/Creating_a_Schedule|Creating a Schedule]].</translate></li>
{{tip|You can also add your backup task to an existing schedule by double-clicking the backup task, selecting the tab '''Schedules''' and adding it to one or more schedules. Additionally, you can group your backup tasks to task groups. For details, see [[Special:MyLanguage/Adding_a_Task_to_the_Task_Group|Adding a Task to the Task Group]].}}
{{<translate><!--T:105--> tip</translate>|<translate><!--T:106--> You can also add your backup task to an existing schedule by double-clicking the backup task, selecting the tab ''Schedules'' and adding it to one or more schedules. Additionally, you can group your backup tasks to task groups. For details, see [[Special:MyLanguage/Adding_a_Task_to_the_Task_Group|Adding a Task to the Task Group]].</translate>}}
<li>Once you have configured a schedule, you must create a new backup event for it. For details, see [[Special:MyLanguage/Creating_a_Backup_Event|Creating a backup event]].</li>
<li><translate><!--T:107--> Once you have configured a schedule, you must create a new backup event for it. For details, see [[Special:MyLanguage/Creating_a_Backup_Event|Creating a Backup Event]].</translate></li>
</ol>
</ol>
You can view the status of your backup jobs by selecting '''Last backup state''' in the ''Main Selection'' window. The backup status overview provides detailed information on the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.
<translate><!--T:108-->
You can view the status of your backup jobs by selecting '''Last Backup State''' in the ''Main Selection'' window. The backup status overview provides detailed information on the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.


=== Backing up a database on 1st instance ===
<!--T:131-->
{{Note|As of [[Special:MyLanguage/SEP_sesam_Release_Versions|4.4.3 ''Beefalo V2'']], you can check the details of your backups online by using new Web UI. For details, see [[Special:MyLanguage/4_4_3_Beefalo:SEP_sesam_Web_UI|SEP sesam Web UI]].}}


In this example, the ''master'' database on 1st instance of the MS SQL Server (''COSINUS'') should be backed up. In this case, you have select '''MS SQL Server''' as a ''task type'' and enter '''COSINUS/master''' as a ''backup source'' when creating a backup task.
=== Backing up a database on 1st instance === <!--T:109-->


{{tip|Use NETBIOS hostname as MS SQL Server hostname.}}
<!--T:110-->
In this example, the ''master'' database on 1st instance of the MS SQL Server (''COSINUS'') should be backed up. In this case, you have select '''MS SQL Server''' as a ''task type'' and enter '''COSINUS/master''' as a ''backup source'' when creating a backup task.</translate>


===Backing up a database on another instance ===
{{<translate><!--T:111--> tip</translate>|<translate><!--T:112--> Use NETBIOS hostname as MS SQL Server hostname.</translate>}}


In this example, a database (second) from another instance of the MS SQL Server (''COSINUS'') should be backed up.<br />
<translate>===Backing up a database on another instance === <!--T:113-->
[[Image:Mssql enterpr man2.JPG|link=]]
 
<!--T:114-->
In this example, a database (second) from another instance of the MS SQL Server (''COSINUS'') should be backed up.</translate><br />
<translate><!--T:115--> [[Image:Mssql enterpr man2.JPG|link=]]</translate>
<br clear=all>
<br clear=all>
In this case, you have select '''MS SQL Server''' as a ''task type'' and enter '''COSINUS/ZWEITE_DB/master''' as a ''backup source'' when creating a backup task.  
<translate><!--T:116--> In this case, you have select '''MS SQL Server''' as a ''task type'' and enter '''COSINUS/ZWEITE_DB/master''' as a ''backup source'' when creating a backup task.</translate>  
 
== {{anchor|list}}Listing SQL Servers from the command line  ==
 
You can list all available MS SQL Servers from the command line by using <tt>-D</tt> (directory) command. Use one of the following commands to list available MS SQL Servers and instances:


;<tt>"sbcmsql:/Net:"</tt>: Lists all net reachable MS SQL Servers.
<div class="mw-collapsible mw-collapsed">
<div class="mw-collapsible mw-collapsed">
''Examples:''
<translate>=={{anchor|list}}Listing SQL Servers from the command line== <!--T:117--></translate>
<div class="mw-collapsible-content">
<div class="mw-collapsible-content">
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
<translate><!--T:118--> You can list all available MS SQL Servers from the command line by using <tt>-D</tt> (directory) command. Use one of the following commands to list available MS SQL Servers and instances:</translate>
;<translate><!--T:119-->
<tt>"sbcmsql:/Net:"</tt>: Lists all net reachable MS SQL Servers.
''Examples:''</translate>
   sbc -D "sbcmsql:/Net:"
   sbc -D "sbcmsql:/Net:"
  "/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
  "/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
  "/MS SQL Server:/COSINUS" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
  "/MS SQL Server:/COSINUS" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
  "/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
  "/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
</div>
</div>


;<tt>"sbcmsql:/NetInstances:"</tt>: Lists all net reachable MS SQL Servers with their instances.
;<translate><!--T:120-->
<div class="mw-collapsible mw-collapsed">
<tt>"sbcmsql:/NetInstances:"</tt>: Lists all net reachable MS SQL Servers with their instances.
''Examples:''
''Examples:''</translate>
<div class="mw-collapsible-content">
 
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
  sbc -D "sbcmsql:/NetInstances:"
  sbc -D "sbcmsql:/NetInstances:"
  "/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
  "/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
Line 178: Line 204:
  "/MS SQL Server:/COSINUS\ZWEITE_DB" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
  "/MS SQL Server:/COSINUS\ZWEITE_DB" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
  "/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
  "/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
</div></div>


;<tt>"sbcmsql:/MS SQL Server:[/<server>[/<instance>]]"</tt>: Lists all on the local server reachable MS SQL Servers and further levels with their instances and databases.
;<translate><!--T:121-->
<div class="mw-collapsible mw-collapsed">
<tt>"sbcmsql:/MS SQL Server:[/<server>[/<instance>]]"</tt>: Lists all on the local server reachable MS SQL Servers and further levels with their instances and databases.
''Examples:''
''Examples:''</translate>
<div class="mw-collapsible-content">
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
   sbc -D "sbcmsql:/MS SQL Server:"
   sbc -D "sbcmsql:/MS SQL Server:"
  "/MS SQL Server:/MIRACULIX" d_ 2017.10.17 18:02:52 2017.10.17 18:02:52
  "/MS SQL Server:/MIRACULIX" d_ 2017.10.17 18:02:52 2017.10.17 18:02:52
Line 193: Line 216:
  "/MS SQL Server:/MIRACULIX/SQLSERVER2005" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
  "/MS SQL Server:/MIRACULIX/SQLSERVER2005" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
  "/MS SQL Server:/MIRACULIX/SQLSERVER2005B" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
  "/MS SQL Server:/MIRACULIX/SQLSERVER2005B" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
</div></div>


;<tt>"sbcmsql:/MS SQL Server:/<server>/<instance>/<database>"</tt>: Lists logical and physical file names - delimiter ''':'''.
;<translate><!--T:122-->
<div class="mw-collapsible mw-collapsed">
<tt>"sbcmsql:/MS SQL Server:/<server>/<instance>/<database>"</tt>: Lists logical and physical file names - delimiter ''':'''.
''Examples:''
 
<div class="mw-collapsible-content">
<!--T:123-->
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
''Examples:''</translate>
 
  sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/(local)"
  sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/(local)"
  /MS SQL Server:/MIRACULIX/(local)/master fb 2016-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
  /MS SQL Server:/MIRACULIX/(local)/master fb 2016-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
Line 227: Line 250:
  /MS SQL Server:/miraculix/(local)/sesam_db/sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db_log.ldf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,
  /MS SQL Server:/miraculix/(local)/sesam_db/sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db_log.ldf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,
</div></div>
</div></div>
<div class="mw-collapsible mw-collapsed">
<div class="mw-collapsible mw-collapsed">
== {{anchor|CLI_bck}}Backing up MS SQL databases from the command line==
<translate>=={{anchor|CLI_bck}}Backing up MS SQL databases from the command line== <!--T:124--></translate>
<div class="mw-collapsible-content">
<div class="mw-collapsible-content">
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
<div class="toccolours mw-collapsible-content" style="background-color: #FFFFFF;">
You can back up SQL databases from the command line by using <tt>-b</tt> (backup) command, as shown in the following examples:
<translate><!--T:125--> You can back up SQL databases from the command line by using <tt>-b</tt> (backup) command, as shown in the following examples:</translate>
<ul><li>Backing up a database on 1st instance, in our example, local database.</span></li>
<ul><li><translate><!--T:126--> Backing up a database on 1st instance, in our example, local database.</translate></span></li>
  sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/(local)/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/(local)/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/(local)/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/(local)/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:sesam_db
{{note|You have to use <tt>sbcmsql:</tt> as a source prefix and NOT <tt>/MS SQL Server:</tt>.}}
{{<translate><!--T:127--> note</translate>|<translate><!--T:128--> You have to use <tt>sbcmsql:</tt> as a source prefix and NOT <tt>/MS SQL Server:</tt>.</translate>}}
<li>Backing up a database on another instance, in our example, second database.</li>
<li><translate><!--T:129--> Backing up a database on another instance, in our example, second database.</translate></li>
  sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/SECOND/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/SECOND/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db
  sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db
Line 244: Line 268:
</div></div>
</div></div>


<div class="noprint">
<translate><div class="noprint">
==See also==
==See also== <!--T:130-->
[[Special:MyLanguage/4_4_3:MS_SQL_Restore|MS SQL Restore]]</div>
[[Special:MyLanguage/4_4_3:MS_SQL_Restore|MS SQL Restore]] – [[Special:MyLanguage/Standard_Backup_Procedure|Standard Backup Procedure]]</div></translate>

Revision as of 13:07, 9 April 2020

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

SEP sesam database module for MS SQL is a part of a client package and enables backing up one or more MS SQL databases; supported backup levels depend on the selected recovery model, as described in section Selecting a recovery model for your MS SQL database.

The source for a MS SQL Server backup must contain the SQL Server database name. If the database belongs to a SQL Server instance, then the server and the instance must be specified explicitly: <server_name>/<instance>/<database>. The names of the instances and databases may be retrieved with SQL Server Management Studio. For details, see Listing SQL Servers from the command line.

Key features

SEP sesam supports the following backup levels of SQL Server:

  • Full backup: A full database backup backs up the whole database stored on MS SQL server, including the transaction log. Full backups provide a duplicate of the database and enable database restore and recovery. Backups are performed at the database instance level.
  • Differential backup: Only the data that has changed since the last database backup is backed up. The full backup upon which a differential backup is based is the base of the differential. Note that restoring a differential backup requires its base to be restored first.
  • Transaction log backup (INCR): SEP sesam uses INCR backup to back up the transaction log. This backup level is relevant only for SQL Server databases that are using the full or bulk-logged recovery model. It requires at least one full backup to be performed before creating any log backups. Incremental (log) backups contain .TRN files and provide all transactions that have changed since the last FULL, DIFF or INCR.
  • Cluster: Microsoft SQL Server on Windows Failover Cluster (active/passive) and AlwaysOn Availability Groups.
SEP Tip.png Tip
Plan your SQL Server backups carefully. SEP recommends using the full recovery model. As for the backup strategy, a typical backup schema may include weekly full database backups, daily differential backups and frequent intervals of the transaction log (INCR) backup, e.g., every 15 minutes. Note that the optimal interval for each backup level depends on the importance of the data, database size, frequency of changes, server workload, etc.

Requirements

To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:

Restrictions

  • In case of Microsoft SQL Server 2012/2014/2016 replication scenario, only the Publisher database is supported for backups. Backup of replicated Distributor or Subscriber database is not supported.
  • A distributed Microsoft SQL environment with AlwaysOn Availability Groups is currently only supported by SEP sesam with the assistance of SEP sesam support.

MS SQL configuration

Specifying the user account for the SEP sesam Client service

  1. On the MS SQL server, configure the SEP sesam Client service with a user account that has appropriate permissions (backup operator) to connect to all SQL databases; typically, this is a user with domain administrator privileges.
  2. Information sign.png Note
    MS SQL backup is not supported if the SEP sesam Client service is running under the Local System account!

    Open the Windows service management console on your MS SQL server, edit the properties of the SEP sesam service, then switch to Log on tab and use valid credentials. Ensure that the specified user has appropriate permissions for backing up and restoring SQL databases.
    System account sql.PNG

  3. After this step restart the SEP sesam daemon. For details, see How to Start and Stop SEP sesam.

Selecting a recovery model for your MS SQL database

MS SQL server provides different recovery models. Before you configure SEP sesam backup for your MS SQL databases, you have to select the relevant recovery model in your SQL Server Management Studio. Recovery models are designed to control transaction log maintenance and will affect how backups are performed and what kind of recovery is possible:
Right click on the database name, select Properties, select the Options tab and then select the recovery model from the drop-down list (the difference between the models is explained below). Click OK to save your selection.

The following three recovery models are available: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. For detailed explanation, refer to Microsoft article Recovery Model Overview.

Simple recovery model
Provides a simple backup that can be used to recover the entire database only to the most recent backup, because the transaction log is not backed up. With this recovery model all transactions since the last backup are lost, thus exposing you to any failures since the last backup completed. The following note recaps the Microsoft article Backup Under the Simple Recovery Model about the data loss exposure:
Information sign.png Note
The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, you should use the full recovery model.
Full recovery model
The most complete recovery model that allows a database to be recovered to any point in time as long as all backup files are usable. Note that transaction log backups are required. SEP sesam provides the transaction log backup with INCR backup level. Therefore, the full recovery model must be used when backing up a database with SEP sesam FULL/DIFF/INCR (transaction log backup) backup level.
Bulk logged model
An adjunct of the full recovery model which does not fully log certain bulk operations, such as BULK INSERT, CREATE INDEX, SELECT INTO, etc. in the transaction log, thus reducing log space consumption. Note that bulk-logged recovery model does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.
Information sign.png Note
Depending on the selected recovery model, you have to select the appropriate SEP sesam backup level. For example, if you want to utilize the transaction log backups (SEP sesam INCR backup), you have to use the full recovery model, otherwise an error is issued, as described in the following MS SQL recovery model types and backup levels section.

MS SQL recovery model types and backup levels

Recovery model Description Supported backup levels Recovery options
Simple The transaction log is not backed up. FULL, COPY, DIFF Allows the recovery of the entire database only to the most recent backup.
Full Transaction log backups are required. FULL, COPY, DIFF, INCR Allows a database to be recovered to any point in time as long as all backup files are usable.
Bulk logged Transaction log backups are required. FULL, COPY, DIFF, INCR Does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.

Depending on SEP sesam backup level (COPY or FULL/DIFF/INCR), the SQL database recovery model and specified backup source, the backup will issue warnings or fail if a transaction log backup (INCR) is performed on database which does not support log backups (is configured with simple recovery model). MS SQL backup success status can be one of the following:

  • COPY and DIFF backups can be used for any SQL database recovery model. These backups should finish successfully with simple, full or bulk-logged model.
  • When a database uses simple recovery model and in the backup task this particular database is set as a source, e.g., my_database, then COPY/FULL/DIFF backups will finish successfully but INCR will fail with message:
  • Error: DB Module: [Incremental backup for database with simple recovery model]
  • When a database uses simple recovery model and in the backup task the source is set to multiple databases (e.g., with specified instance or as 'all'), which means that this database is backed up in one session together with other databases, then only COPY and DIFF backups are successful. FULL/INCR backup will end with warning database not logged for the respective database:
  • Warning: Item [<database_name>] is not logged. Recovering may fail.
  • As of SEP sesam v. 4.4.3 Tigon V2, there are some exceptions when logging the backup status for the system databases master, model and msdb. If these databases are configured with simple recovery model and a FULL/INCR backup is performed, the missing transaction log backup for these system DBs is ignored and no warnings are issued during the backup.
  • This new no-warning behavior is based on recommended backup strategy for system databases: typically, master and msdb databases are using simple recovery mode (although the recovery mode can be set to FULL for master DB, transaction log backup cannot be performed); the model database sets the default recovery model of new databases and should have the recovery mode set to FULL. For details, see Microsoft article Back Up and Restore of System Databases (SQL Server).
SEP Tip.png Tip
If you have a mixture of databases on the same SQL Server, i.e., some with simple and other with full recovery mode, create separate backup tasks for DBs with simple and DBs with full model.

Transaction log truncation

SQL Server database transaction log records all transactions and related database modifications. Transaction log backups are essential if you want to recover the database to a specific point in time or to the point of failure. Point-in-time restore is only possible with SQL database full recovery model (see section Selecting a recovery model for your MS SQL database, which requires scheduled FULL (database backup) as well as INCR (log) backups to get the unbroken log chain that enables the database restore to any point in time.

After the transaction log (INCR) backup, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portions (these are completed transactions that are no longer required for recovery) of the transaction log thus preventing the transaction log to grow and fill up. Note that it cannot be truncated until all of its records are captured in a log (INCR) backup! Therefore, under the SQL database full recovery model the log files will not be truncated at all without the INCR backup.

As specified in the Microsoft article The Transaction Log (SQL Server), log truncation typically occurs automatically after the following events:

  • When simple recovery model is used, after a checkpoint.
  • When full recovery model or bulk-logged recovery model is used, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
Information sign.png Note
  • Some factors, such as a long-running transaction, can delay log truncation, therefore you should monitor log size regularly. You should not truncate the transaction log manually as this will break the backup chain.
  • Transaction log (INCR) backup is based on FULL backup, so make sure that at least one successful full backup exists before creating any log backups.

For details on managing the transaction logs, see Microsoft article The Transaction Log (SQL Server).

Adding the MS SQL client to SEP sesam environment

Once you have downloaded the SEP sesam package, configure your MS SQL client by adding it to the SEP sesam environment: Main selection -> Components -> Topology -> New Client -> add your MS SQL client. For details, see Configuring Clients.

Information sign.png Note
Before proceeding, you should validate that you can backup to SEP sesam. Run a test Path backup manually to ensure that the backups work. For details on typical backup configuration, see Standard Backup Procedure.

Configuring MS SQL database backup

To back up your SQL server data, you have to create a backup task (select the data to be backed up), then set up a backup schedule (specify when you want to back up your data), and link a backup event to it – in this 3rd step you select the appropriate SEP sesam backup level depending on the used recovery model of your SQL databases. For example, if you want to utilize the transaction log backups (SEP sesam INCR backup), you have to use the full recovery model, otherwise an error is issued. For details, see section Selecting a recovery model for your MS SQL database. For general details on how to configure a backup and its prerequisites, see Standard Backup Procedure.

SEP Tip.png Tip
If you have a mixture of databases on the same MS SQL Server, i.e., some with simple and other with full recovery mode, create separate backup tasks for DBs with simple and DBs with full model.

Create a new backup task to back up all or only selected databases on an instance.

  1. From Main Selection -> Tasks -> By Clients, select MS SQL client and click New Backup Task. The New Backup Task window opens.
  2. Click the Browse button (big arrow) and select either the single database or all MS SQL databases you want to back up. You can also enter the source manually, as follows:
    • <hostname>/<instance>/<DB_name>: Specify a backup of a specific database; the database name is case-sensitive.
    • all: Specify a backup of all databases on an instance.

    Note that if you select the source by browsing, the task type and task name are set automatically. If you enter the source manually, you have to enter the task name and select MS SQL Server as the task type.
    MS SQL bck task Beefalo V2.jpg

  3. You may want to exclude some databases on an instance from the backup. Use the Exclude List Editor to specify the exclusions by using regular expressions. Add the exclusion patterns one by one followed by a comma. You can also use a file to define a backup source to be excluded. For details on various exclude options, see Creating Exclude List.
  4. Optionally, switch to the Options tab and enter additional options for backup in the Backup options (previously Save options) field, if required.
    Click OK to create the task.
  5. If you want to start the newly created task immediately, right-click the name of the task and click Immediate Start. If you want to create a periodic backup, you have to create a schedule for your backup task: Click New Schedule under Main Selection -> Scheduling -> Schedules and set up a schedule. For more details, see Creating a Schedule.
  6. SEP Tip.png Tip
    You can also add your backup task to an existing schedule by double-clicking the backup task, selecting the tab Schedules and adding it to one or more schedules. Additionally, you can group your backup tasks to task groups. For details, see Adding a Task to the Task Group.
  7. Once you have configured a schedule, you must create a new backup event for it. For details, see Creating a Backup Event.

You can view the status of your backup jobs by selecting Last Backup State in the Main Selection window. The backup status overview provides detailed information on the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc.

Information sign.png Note
As of 4.4.3 Beefalo V2, you can check the details of your backups online by using new Web UI. For details, see SEP sesam Web UI.

Backing up a database on 1st instance

In this example, the master database on 1st instance of the MS SQL Server (COSINUS) should be backed up. In this case, you have select MS SQL Server as a task type and enter COSINUS/master as a backup source when creating a backup task.

SEP Tip.png Tip
Use NETBIOS hostname as MS SQL Server hostname.

Backing up a database on another instance

In this example, a database (second) from another instance of the MS SQL Server (COSINUS) should be backed up.
Mssql enterpr man2.JPG
In this case, you have select MS SQL Server as a task type and enter COSINUS/ZWEITE_DB/master as a backup source when creating a backup task.

Listing SQL Servers from the command line

You can list all available MS SQL Servers from the command line by using -D (directory) command. Use one of the following commands to list available MS SQL Servers and instances:

"sbcmsql:/Net:"
Lists all net reachable MS SQL Servers.

Examples:

 sbc -D "sbcmsql:/Net:"
"/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
"/MS SQL Server:/COSINUS" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
"/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:01 2017.10.17 18:02:01
"sbcmsql:/NetInstances:"
Lists all net reachable MS SQL Servers with their instances.

Examples:

sbc -D "sbcmsql:/NetInstances:"
"/MS SQL Server:/BUCHFIX2" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
"/MS SQL Server:/COSINUS" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
"/MS SQL Server:/COSINUS\ZWEITE_DB" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
"/MS SQL Server:/SEHNIX" d_ 2017.10.17 18:02:26 2017.10.17 18:02:26
"sbcmsql:/MS SQL Server:[/<server>[/<instance>]]"
Lists all on the local server reachable MS SQL Servers and further levels with their instances and databases.

Examples:

 sbc -D "sbcmsql:/MS SQL Server:"
"/MS SQL Server:/MIRACULIX" d_ 2017.10.17 18:02:52 2017.10.17 18:02:52
sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX"
"/MS SQL Server:/MIRACULIX/(local)" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SECOND" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SQLSERVER2005" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SQLSERVER2005B" d_ 2017.10.17 18:03:11 2017.10.17 18:03:11
"sbcmsql:/MS SQL Server:/<server>/<instance>/<database>"
Lists logical and physical file names - delimiter :.

Examples:

sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/(local)"
/MS SQL Server:/MIRACULIX/(local)/master fb 2016-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/model fb 2016-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/msdb fb 2016-10-14 01:54:05.240 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/msdb2 fb 2016-10-10 12:55:46.030 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/sesam_db fb 2016-09-20 14:17:04.730 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/sesam_db2 fb 2016-09-20 16:21:01.030 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/tempdb fb 2016-10-17 18:06:27.747 07.10.17 18:06:33. 4096 -,  
not_saveable only for temporary operations
/MS SQL Server:/MIRACULIX/(local)/testdb fb 2016-09-24 16:19:36.123 07.10.17 18:06:33. 4096 -,
sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/SECOND"
/MS SQL Server:/MIRACULIX/SECOND/AdventureWorks fb 2016-08-16 16:17:06.717 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/AdventureWorksDW fb 2016-08-16 16:16:45.640 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/master fb 2016-04-08 09:13:36.390 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/model fb 2016-04-08 09:13:36.390 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/msdb fb 2016-10-14 01:54:05.240 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam_db fb 2016-10-10 13:26:53.310 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam_db2 fb 2016-10-10 15:09:26.200 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam2 fb 2016-10-10 15:13:49.607 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/tempdb fb 2016-10-17 18:05:41.967 07.10.17 18:05:54. 4096 -,  
not_saveable only for temporary operations
sbc -D "SBCMSQL:MS SQL Server:/miraculix/(local)/sesam_db" 2>nul
/MS SQL Server:/miraculix/(local)/sesam_db/sesam_db:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db.mdf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,
/MS SQL Server:/miraculix/(local)/sesam_db/sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db_log.ldf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,

Backing up MS SQL databases from the command line

You can back up SQL databases from the command line by using -b (backup) command, as shown in the following examples:

  • Backing up a database on 1st instance, in our example, local database.
  • sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/(local)/sesam_db sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/(local)/sesam_db sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/sesam_db sbc -b -s @sesam_db.save -v 3 sbcmsql:sesam_db
    Information sign.png Note
    You have to use sbcmsql: as a source prefix and NOT /MS SQL Server:.
  • Backing up a database on another instance, in our example, second database.
  • sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/SECOND/sesam_db sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db