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/2019/2022
• 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
  • Welcome
  • Where to Back Up
  • SQL Server Instance
  • Databases
  • Compression and Encryption Options
  • Retention Policy
  • Schedule Options
  • Pre/Post Actions or Backup Chain
  • Notifications
• Run the Backup Plan
• Edit the Backup Plan
• Clone the Backup Plan
• Delete the Backup Plan

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. You will need Ultimate Backup Agent edition to see this feature.

Welcome

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.

Click Next. | Top |

Where to Back Up

Select the backup storage. Select what backup you are planning to use, local, cloud or hybrid.

If you use a local or cloud backup type, select the cloud storage or the local storage for the backup plan.

If you use a hybrid backup type, select the cloud storage and the local storage for the backup plan.

Click Next. | Top |

SQL Server Instance

10. 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 to perform SQL Server database backup. 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.

Click Next. | Top |

Databases

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.

• Select copy-only backup if necessary. If copy-only backup is enabled, the differential backup becomes unavailable.

Click Next.

| Top |

Compression and Encryption

Specify the compression 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

Click Next.

| Top |

Retention Policy

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 retention options option

• If you want to set the custom retention policy for the backup plan, select Specify custom retention policy for the backup plan option. You can configure different retention policy for the local backup in case of you use hybrid backup plan.

• 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
• Keep number of versions for each file. Select this check box if you want to keep selected number of versions in backup storage. It is highly recommended to keep this check box selected for backup plans with sensitive data

Click Next.

Schedule Options

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 longer than check box, then specify the plan run duration
• If you want to receive an alert in case the backup plan fails to run, select the Mark plan as overdue after check box, then specify the alert delay period
• 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

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

20. Click Next.

| Top |

Pre/Post Actions

Specify the actions to be executed before and/or after the backup plan run.

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

Click Next.

| Top |

Backup Chain

This option will be available in case the pre- and post actions are not allowed. Specify the backup chain options.

• To chain the backup plan with another plan, select the plan name in the drop-down menu.
  • Select the Run the selected plan if this backup plan completes with success option if you want to run the specified plan only if the backup plan was executed without any errors or warnings.
  • Select the Execute regardless of this backup plan completion result option if you want the chained backup plan to be launched regardless of the backup termination results. Also you can force full backup or use the current plan settings for the chained plan.

Click Next.

| Top |

Notifications

Specify notification settings for backup plan results. You can use the company notification settings or customize them as needed: specify t he required recipients and customize the notifications on different backup plan results: Success Failure Warning

Select User receives a notification email when backup completes if you want to notify your users about the backup plan results. You can select whether users will be notified about backup failure or any backup result.

If you want the backup plan record to be added to Windows Event Log, select 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 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.

Click the Next, then click Save to finish the wizard.

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.