4 4 3:MS SQL Backup

From SEPsesam
Jump to: navigation, search
Other languages:
Deutsch • ‎English

Copyright © SEP AG 1999-2022. 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 Beefalo V2/5.0.0 Jaglion. For previous documentation version(s), check MS SQL.


Overview

The SEP sesam database module for MS SQL is part of the Client package and enables the backup of one or more MS SQL databases (the feature to backup multiple databases will be deprecated in the future version, see below). The supported backup levels depend on the selected recovery model, as described in the 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, the server and instance must be explicitly specified: <server_name>/<instance>/<database>. The names of the instances and databases can be retrieved using SQL Server Management Studio. For details, see Listing SQL Servers from the command line.

Key features

SEP sesam supports the following SQL Server backup levels:

  • Full backup: A full database backup backs up the entire database stored on the 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, on which a differential backup is based, is the basis for the differential backup. Note that restoring a differential backup requires its base to be restored first.
  • Transaction log backup (INCR): SEP sesam uses the INCR backup to back up the transaction log. This backup level is only relevant for SQL Server databases that use the full or bulk-logged recovery model. It requires that at least one full backup is 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 scheme might 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.

Feature to be deprecated in future release: Backup source all

Specifying backup source all, which allows you to back up multiple databases with one task, will be deprecated in future versions. It will be deprecated because it is generally better to back up only one database per task than to use multiple databases. However, you can add the tasks for backing up MS SQL databases to a task group and then trigger the start of all MS SQL tasks belonging to this group with a single event. The tasks are then processed according to the number of available channels. For details, see Adding a Task to the Task Group.

Requirements

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

Restrictions

  • In the case of a Microsoft SQL Server 2012/2014/2016 replication scenario, only the Publisher database is supported for backups. Backup of the replicated Distributor or Subscriber database is not supported.

MS SQL configuration

Setting up 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 the 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 the Log on tab and use valid credentials. Ensure that the specified user has the appropriate permissions for backing up and restoring SQL databases. For more information on SEP sesam user types, see also User Roles and Permissions.
    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 appropriate recovery model in your SQL Server Management Studio. The recovery models are designed to control transaction log maintenance and affect how backups are performed and what type of recovery is possible:
Right click 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 the 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 was 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 unsuitable for production systems where 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 addition to the full restore model where certain bulk operations, such as BULK INSERT, CREATE INDEX, SELECT INTO, etc. are not fully logged in the transaction log, thus reducing log space consumption. Note that the 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 use 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 section MS SQL recovery model types and backup levels.

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 the SEP sesam backup level (COPY or FULL/DIFF/INCR), the SQL database recovery model and the specified backup source, the backup will issue warnings or fail if a transaction log backup (INCR) is performed on a database that 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 complete successfully using the simple, full or bulk-logged model.
  • When a database uses a simple recovery model and the backup task has that particular database set as the source, e.g., my_database, then COPY/FULL/DIFF backups will complete successfully, but INCR will fail with a message:
  • Error: DB Module: [Incremental backup for database with simple recovery model]
    
  • When a database uses a simple recovery model and the backup task has the source set to multiple databases (e.g., with specified instance or as all), which means that this database is backed up in one session along with other databases, then only COPY and DIFF backups are successful. (Note that the feature to backup multiple databases with one task will be deprecated in the future version, see above.) The FULL/INCR backup will end with the warning database not logged for the respective database:
  • Warning: Item [<database_name>] is not logged. Recovering may fail.
    
  • There are some exceptions to logging the backup status for the system databases master, model and msdb. If these databases are configured with the simple recovery model and a FULL/INCR backup is performed, the missing transaction log backup for these system DBs will be ignored and no warnings will be issued during the backup.
  • This new no-warning behavior is based on the recommended backup strategy for system databases: typically, master and msdb databases use the simple recovery mode (although the recovery mode for master DB can be set to FULL, no transaction log backup can be performed); the model database sets the default recovery model for new databases and should have the recovery mode set to FULL. For details, see the 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 recovery mode and some with full recovery mode, create separate backup tasks for DBs with simple and DBs with full model. You can add the tasks for backing up MS SQL databases to a task group and then trigger the start of all MS SQL tasks belonging to this group with a single event. For details, see Adding a Task to the Task Group.

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 the SQL database full recovery model (see section Selecting a recovery model for your MS SQL database, which requires both 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 needed for recovery) of the transaction log to prevent 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:

  • If the simple recovery model is used, after a checkpoint.
  • When the 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 the log size regularly. You should not truncate the transaction log manually as this would 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 transaction logs, see the Microsoft article The Transaction Log (SQL Server).

Adding the MS SQL client to the 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, check that you can back up 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 the 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 recovery model used for your SQL databases. For example, if you want to use the transaction log backups (SEP sesam INCR backup), you have to use the full recovery model, otherwise an error will be issued. For details, see the 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.
  • If you want to encrypt the backup of MS SQL databases, you have to create a separate backup task with encryption for each database. Note that if you create an encrypted backup of multiple MS SQL databases, you will not be able to restore a single database, but only all databases together.

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

Information sign.png Note
The feature to back up multiple databases with one task will be deprecated in the future version, see above.
  1. From Main Selection -> Tasks -> By Clients, select the MS SQL client and click New Backup Task. The New Backup Task window opens.
  2. Click the Browse button (large 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 the different exclusion options, see Creating Exclude List.
  4. Optionally, switch to the Options tab and enter additional options for the backup in the Backup options field.
    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 then 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 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. You can also 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.

Backing up a database on the 1st instance

In this example, the master database on the 1st instance of the MS SQL Server (COSINUS) should be backed up. In this case, you have select MS SQL Server as the task type and enter COSINUS/master as the backup source when you create 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 to select MS SQL Server as the task type and enter COSINUS/ZWEITE_DB/master as the backup source when creating a backup task.

Monitoring backups

You can view the status of your backup jobs in the GUI (Monitoring -> Last Backup State or Job State -> Backups) or SEP sesam Web UI. The backup status overview provides detailed information about 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.

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 the 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 the -b (backup) command, as shown in the following examples:

  • Backing up a database on the 1st instance, in our example, the 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 the source prefix and NOT /MS SQL Server:.
  • Backing up a database on another instance, in our example, the 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