Source:MS SQL Backup: Difference between revisions

From SEPsesam
(Marked this version for translation)
No edit summary
Line 40: Line 40:
*<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>
*<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>
*<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>
*<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>
*<translate><!--T:19--> '''Cluster''': Microsoft SQL environment with '''Always On Failover Cluster''' (active/passive).</translate>
*<translate><!--T:19--> '''Cluster''': Microsoft SQL Server on'''Windows Failover Cluster''' (active/passive) and '''Always On Availability Groups'''.</translate>
{{<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>}}
{{<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>}}



Revision as of 11:17, 9 May 2019

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 Grolar. 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 onWindows Failover Cluster (active/passive) and Always On 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 Always On availability groups is currently not supported.

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 taks 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 backup 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.png

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

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

See also

MS SQL Restore