MS SQL Server Backup

Managed Backup Service allows you to perform MS SQL Server native backups (full, differential, transaction log) and restores with the benefits of encryption.

It is recommended to create only one backup plan for every MS SQL Server database to avoid one plan interfering with another. In case you need to back up the SQL Server data to several locations, select the hybrid backup on the Select Backup Storage step.

Features:

  • Flexible selection of databases for backup
  • Compression (native SQL Server compression if supported, otherwise, a custom compression)
  • Encryption
  • Copy-only backup (available on the Backup Agent only for now)
  • Flexible schedules, pre-defined schedule templates
  • Restore databases directly to MS SQL Server
  • Restore databases as files (.mdf, .ldf)
  • Point-in-time restore.

SQL Server performs backup and restore operations following a recovery model defined for the database, specifying how transactions are logged, whether or not this log requires (and allows) backups, as well as what kinds of restore operations are available. Transaction log backup is allowed for Full and Bulk-Logged recovery models only.

Note: Only local MS SQL Server instances can be configured for backup.

Supported for:

  • MS SQL Server 2008/2012/2014/2017
  • MS SQL Server Express

License: MS SQL Server, MS SQL Server + MS Exchange, Ultimate (Check out Backup Agent Editions)

This chapter covers the following topics:

Create MS SQL Server Backup Plan

To create a new MS SQL Server backup plan, proceed as follows:

  1. Open the Management Console.
  2. On Computers select Remote Management if you use legacy main menu, or open Backup > Computers page in the new main menu.
  3. Find the required computer, then click the Configure icon in the Backup Plan Status colimn.
  4. On the side panel, click + or +Add New Plan, then select the MS SQL item.

6. Specify the plan name. If you want to keep the backup plan configuration in the backup storage, select the Save backup plan configuration to the backup storage check box.

If you use encryption in the backup plan, mind that for security reasons the encryption password is not saved anywhere. Keep the encryption password separately in a safe place

  1. Click Next.
  2. Select the backup storage. If you use a hybrid backup type, select the cloud storage and the local storage for the backup plan.

  1. Click Next.
  2. Select the SQL server instance to back up:
  • In the SQL Server Instance drop-down list, select the instance for the backup plan
  • In the Authentication Type drop-down list, select the authentication method:
    • Select the Windows authentication item, if you want to use the Windows principal token
    • Select SQL Server Authentication item, if you want to use proprietary SQL Server authentication. In case you use SQL Server authentication, specify the user name and password
  • Select the Check if the specified account has necessary permissions to perform backup check box if you want to make sure the specified credentials are granted with sufficient permissions. Once you click Next, the credentials will be checked if this account is granted enough permissions
  • To connect to SQL Server instance using SSL, select the Use secure connection (SSL/TLS) check box.

  1. Click Next.
  2. Select databases for the backup plan. The following options are available:
  • Back up all databases. Select this option if you want to back up all databases on the selected instance, including system and user databases
  • Back up all user databases only. Select this option to back up only user databases. In this case, system SQL Server databases (master, model, msdb) will not be included in the backup plan.
  • Back up selected databases only. Select this option if you want to select databases for the backup plan manually, then select databases from the backup in the table below.

  1. Click Next.
  2. Specify the compressions and encryption options. The following options are available:
  • To enable backup compression, select Enable compression check box
  • To encrypt data being backed up, select the Enable encryption check box.
    • In the Algorithm drop-down menu, select the encryption key length
    • In the Password field, specify the encryption password, then confirm it in the Confirm password field

  1. Click Next.
  2. Specify the retention policy settings. The following settings are available:
  • If you want to use the default retention policy for the backup plan, select Use default options specified for the whole product option
  • If you want to set the custom retention policy for the backup plan, select Specify custom retention policy for plan option
  • To specify the number of full backups to be kept in backup storage, select the Keep number of full backups check box, then specify the number in the field below
  • To specify the delay period for database backup version deletion, select the Delay purge for check box, then specify the delay period
  • Select the Delete versions older than check box if you want to keep the restricted number of database backup versions in backup storage. In the fields below, specify the criteria for the deletion of old database versions
  • Always keep the last version. Select this check box if you want to keep the last database backup version in backup storage. It is highly recommended to keep this check box selected for backup plans with sensitive data

  1. Click Next.
  2. Specify the schedule options for the backup plan.
  • If you plan to run the backup plan manually, select the No schedule option
  • If you want your backup plan to run on a particular date and time, select the Specific date option, then specify the date and time you want your backup plan to be run
  • If you intend to execute this backup plan on a constant periodic basis using the built-in templates, select the Recurring (predefined templates) option. Configure these settings on the next step
  • If you intend to execute this backup plan on a constant periodic basis, select the Recurring (advanced) option. Configure these settings on the next step
  • To stop the backup plan in case it continues suspiciously long, select Stop the plan if it runs for check box, then specify the plan run duration
  • To run the backup plan after the computer is on in case the backup plan run has been missed, select the Run missed scheduled plan immediately when computer starts up check box
  • If you want to receive an alert in case the backup plan fails to run, select the Alert plan as overdue after check box, then specify the alert delay period.

  1. Click Next. In case you selected one of the recurring schedule options, configure schedule settings.

In case you selected the schedule from the built-in templates, the following options are available:

In case you selected the advanced recurring settings, the following options are available:

  1. Click Next.
  2. Specify the actions to be executed before and/or after the backup plan run.
  • Select the Synchronize local repository with backup storage before plan runs check box if you want your locale repository database to be synchronized with the backup storage
  • To specify the action that will be performed before the backup plan starts, select Execute this command before backup runs: check box.
  • In the field below, specify the path to the script to be executed before the backup plan.
  • Specify the conditions of the script's execution:
    • Exit backup if pre-backup action fails option if you do not want the backup plan to run if the pre-backup script fails
    • Select the Continue backup plan if pre-backup action failed option to execute the backup plan regardless of the pre-backup script execution result
  • To specify the action that will be performed after the backup plan is completed, select the Execute this command after backup completes: check box
    • Select the Execute post-backup action if backup has been successfully completed option if you want to run it only if the backup plan is successfully completed
    • Select Execute post-backup action in any case (regardless of the backup result) option if you want the script to execute regardless of the backup plan execution results
  • To chain the backup plan with another plan, select Execute following plan after backup completes: check box, then select the plan name in the drop-down menu
    • Select Execute the specified plan only if backup has been successfully completed option if you want to run the specified plan only if the backup plan is completed without errors or warnings
    • Select Execute the specified plan regardless of the backup result option if you want to run the chained backup plan regardless of the current backup plan execution results.

  1. Click Save.
  2. Specify the notification settings. The following settings are available:
  • To receive the notification after the backup plan completion, select the I want to receive notification email when backup completes check box.
    • Select the When backup fails option if you want to receive the notification message only in case of the backup plan failure.
    • Select the In all cases option if you want to receive the notification message in any case.
  • If you want the backup plan record to be added to Windows Event Log, select on Add entry to Windows Event Log when backup completes check box.
    • Select the When backup fails option if you want to receive the notification message only in case of the backup plan failure.
    • Select the In all cases option if you want the entry to be put in Windows Event Log in any case.
  1. Once you are finished with all settings, click Save.

Run Backup Plan

To Run the Existing Backup Plan

  1. On Computers select Remote Management if you use legacy main menu, or open Backup > Computers page in the new main menu.
  2. Find the required computer, then click the Configure icon in the Backup Plan Status colimn.
  3. On the side panel, find the plan to run, then click on it.
  4. Click the Play button.

Edit Backup Plan

To Edit the Existing Backup Plan

  1. On Computers select Remote Management if you use legacy main menu, or open Backup > Computers page in the new main menu.
  2. Find the required computer, then click the Configure icon in the Backup Plan Status colimn.
  3. On the side panel, expand the plan you want to edit, then click Edit.
  4. Once you made the required changes, click Save.

Clone Backup Plan

To Clone the Existing Backup Plan

  1. On Computers select Remote Management if you use legacy main menu, or open Backup > Computers page in the new main menu.
  2. Find the required computer, then click the Configure icon in the Backup Plan Status colimn.
  3. On the side panel, expand the plan you want to clone, then click Clone.
  4. In the field below, specify the name for the plan clone.
  5. Click Save.

Delete Backup Plan

To Delete the Existing Backup Plan

  1. On Computers select Remote Management if you use legacy main menu, or open Backup > Computers page in the new main menu.
  2. Find the required computer, then click the Configure icon in the Backup Plan Status colimn.
  3. On the side panel, expand the plan you want to delete, then click Delete.
  4. In the dialog box, click OK to confirm the deletion.