MS SQL Server Backup

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

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 in accordance with 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 MS SQL Server Backup Plan

  1. Open the Management Console.
  2. In the RMM menu, select the Remote Management item.
  3. In the computer list, find the computer you want the MS SQL backup plan to be created, then click the Settings icon.
  4. Select Show Plans.
  5. In the side panel, click +, 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 the encryption password is not saved in a backup storage due to security reasons. Keep it separately in a safe place

  1. Click Next.
  2. Select the backup storages. 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 own SQL Server authentication. In case you use SQL Server authentication, specify 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 for validity
  • To connect to an 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 object in the backup plan, select Enable encryption check box.
    • In Algorithm drop-down menu, select the encryption key length
    • In Password field, specify the encryption password, then confirm it in 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 a 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 a backup storage. In the fields below, specify the criteria for 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 a 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 No schedule option
  • If you want your backup plan to run on a particular date and time, select Specific date option, then specify the date and time you want your backup plan to be run
  • If you your backup plan to be run on a constant periodic basis using the built-in templates, select Recurring (predefined templates) option. These settings will be specified on the next step
  • If you want your backup plan to be run on a constant periodic basis, select Recurring (advanced) option. These settings will be specified on the next step
  • To stop the backup plan in case if 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 Alert plan as overdue after check box, then specify the alert delay period.

  1. Click Next. In case you selected one of recurring options, specify the recurring 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 Synchronize local repository with backup storage before plan runs check box if you want your locale repository database to be syncronized 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 Continue backup plan if pre-backup action failed option if you want the backup plan to launched regardless of the pre-backup scirpt execution result
  • To specify the action that will be performed after the backup plan is completed, select Execute this command after backup completes: check box
    • Select 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 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 has been successfully completed
    • 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 I want to receive notification email when backup completes check box.
    • Select When backup fails option if you want to receive the notification message only in case of the backup plan failure.
    • Select 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 When backup fails option if you want to receive the notification message only in case of the backup plan failure.
    • Select 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. In the RMM menu, select Remote Management item.
  2. Find a required computer, then click the Settings icon.
  3. In the drop-down menu, select Show Plans.
  4. In the side panel, find the plan to run, then click on it.
  5. Click the Play button.

Edit Backup Plan

To Edit the Existing Backup Plan

  1. In the RMM menu, select Remote Management item.
  2. Find a required computer, then click the gear icon.
  3. In the drop-down menu, select Show Plans.
  4. Click on the plan you want to edit, then click Edit.
  5. Once you made the required changes, click Save.

Clone Backup Plan

To Clone the Existing Backup Plan

  1. In the RMM menu, select Remote Management item.
  2. Find a required computer, then click the gear icon.
  3. In the drop-down menu, select Show Plans.
  4. Click on the plan you want to edit, then click Clone.
  5. In the field below, specify the name for the plan clone.
  6. Click Save.

Delete Backup Plan

To Delete the Existing Backup Plan

  1. In the RMM menu, select Remote Management item.
  2. Find a required computer, then click the gear icon.
  3. In the drop-down menu, select Show Plans.
  4. Click on the plan you want to delete, then click Delete.
  5. In the dialog box, click OK to confirm the deletion.