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
• 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 License Types)

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
• Force Full Backup
• 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. Open Backup > Computers
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 the Ultimate Backup Agent license 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 |

Select SQL Server Instance

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 username and password.
• Select the Check if the specified account has necessary permissions to perform backup checkbox 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) checkbox.

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

Compression

Managed Backup offers compression to reduce the storage space required for your backup and to speed up the upload process to the target storage.

Encryption

You can protect your backup by encrypting its contents. Managed Backup supports AES encryption with key sizes of 128, 192, and 256 bits. A larger key size provides stronger encryption but may increase the time required for processing your backup. For more details on AES encryption, refer to the Advanced Encryption Standard.

To protect your backup contents with encryption, select the Enable encryption checkbox. Application supports AES encryption of 128, 192 and 256 bit key length. Select the appropriate key length in the Algorithm drop-down menu

• Specify the encryption password in the Password field, then confirm the password in the Confirm field. Mind to keep the encryption password in a safe place. Pay attention, if Password Recovery Service is not enabled in the Management Console, then if the encryption password is lost or forgotten, the encrypted backup cannot be restored. Password recovery Service requires the Two-factor Authentication (2FA) enabled.
• In the Hint field, specify some information that could help to recall the password in case you forget it.

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 a different retention policy for the local backup in case you use the hybrid backup route.

• Select the Delete versions older than checkbox 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 checkbox if you want to keep selected number of versions in backup storage. It is highly recommended to keep this checkbox 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 checkbox, 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 checkbox, 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 checkbox.

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 pre and post-actions for your backup plan. Usually, these are scripts that perform particular jobs before or after your data is backed up. The following settings are available:

• To specify the action that will be performed before the backup plan starts, select the Pre-backup action check box.
• Specify the path to the script to be run as a pre-backup action.
• Specify the conditions of pre-action run:
  • Select Do not run the backup plan if the pre-backup action fails option if you do not want the backup plan to be launched if the pre-backup action fails.
  • Select Run the backup plan if the pre-backup action fails option if you want the backup plan to launch regardless of the pre-backup action result.
• To specify the action that will be performed after the backup is completed, select the Post-backup action check box.
  • Select Execute the post-backup action only if the backup plan completes successfully option if you want to run it only if the backup was executed without any errors or warnings.
  • Select Execute the post-backup action regardless of the backup plan result option if you want the post-action to be launched regardless of the backup termination results.
• To chain the backup plan with another plan, select Backup chain check box, then select the backup or restore plan name in the drop-down menu.
  • Select 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 Run the selected plan regardless of this backup plan completion result option if you want the chained backup plan to be launched regardless of the backup termination results. Select the mode for the current chained backup plan:
  • Force full backup. Full backup will be forced for the chained backup plan.
  • Use settings of the current backup plan. Chained backup plan will be run as full or incremental, according to this backup plan run.

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

Notifications

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

• Success
• Failure
• Warning

In case you select to customize notifications, select the recipients for different events.

Select I want to receive a notification email if you want to notify your users about the backup process. You can select whether users will be notified about backup failure or about any backup result.

• Select If the backup plan fails or completes with warnings option if you want to receive the notification message 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.

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 If the backup plan fails or completes with warnings option if you want to receive the notification message 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.

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

Run Backup Plan

To Run the Existing Backup Plan:

1. Open Backup > Computers.
2. Find the required computer, then click the Configure icon in the Backup Plan Status column.
3. On the side panel, navigate to the Backup Plans (Beta) tab
4. Find the plan to run.
5. Click the Start button to run the backup plan. You can expand this control to use the following options:
   • Run Backup
   • Force Full Backup
   • Force Synthetic Full Backup

Force Full Backup

To Force Full Backup 1.1. Open Backup > Computers. 2. Find the required computer, then click the Configure icon in the Backup Plan Status column. 3. On the side panel, navigate to the Backup Plans (Beta) tab 4. Find the plan to force. 5. Expand the Start control and click Force Full Backup.

Edit Backup Plan

To Edit the Existing Backup Plan

1. Open Backup > Computers.
2. Find the required computer, then click the Configure icon in the Backup Plan Status column.
3. On the side panel, find the plan you want to edit, then click the edit icon.

5. Once you made the required changes, click Save.

Clone Backup Plan

To Clone the Existing Backup Plan

1. Open Backup > Computers.
2. Find the required computer, then click the Configure icon in the Backup Plan Status column.
3. On the side panel, find the plan you want to clone, then click the clone icon.

4. Specify the name of the plan clone.
5. Click Save.

Delete Backup Plan

To Delete the Existing Backup Plan

1. Open Backup > Computers.
2. Find the required computer, then click the Configure icon in the Backup Plan Status column.
3. On the side panel, find the plan you want to delete, then click the deletion icon.

4. In the dialog box, click OK to confirm the deletion.

| Top |