How to Back Up MS SQL Server

Backup Agent for Windows enables you to perform backup of Microsoft SQL Server. The backup software uses native SQL Server backup procedures to perform database backup and then moves it directly to the cloud. User doesn't need MS SQL Server administration skills to perform a successful backup every time it's required.

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 try to select a hybrid backup on welcome screen of the backup wizard.

Learn more how to download a required Backup Agent editon in the Downloads section.

This chapter cover the following topics:

• Create Backup Plan
• Edit Backup Plan
• Delete Backup Plan

Create Backup Plan

To create a new VMware backup plan in the legacy backup format, select VMware Backup (Legacy).

Learn Backup Wizard steps:

• Select Backup Route
• Select Backup Storage
• Plan Name
• Advanced Options
• Select MS SQL Server instance
• Select Databases
• Compression and Encryption Options
• Retention Policy
• Schedule
• Pre / Post Actions
• Notifications and Logging

Select Backup Route

Specify the backup route. The following options are available:

• Local or Cloud Backup (default option). Select this option if you want to back up your data directly to a local or cloud storage.

• Hybrid Backup. This option implies an intermediate local storage before backing up your data to a cloud storage. Select this option if you want to back up your data locally first.

Select Backup Storage

Select a storage account for the backup plan from the list of available backup storages.

Click Next.

Plan Name

Specify the name for the backup plan.

Click Next.

| Top |

Advanced Options

Specify advanced options for the backup plan. Advanced option set depends on the selected backup storage.

Additional Advanced Options for Amazon S3

1. If your backup storage destination is Amazon S3, select the S3 storage class for the backup plan:

• Standard
• Intelligent-Tiering
• Standard-IA
• One Zone-IA
• Glacier Instant Retrieval
• Glacier Flexible Retrieval (formerly S3 Glacier)
• Glacier Deep Archive

Usage of different storage classes for different backups is the subject of optimizing your storage costs.

2. Select Use S3 Transfer Acceleration to accelerate file transfer for an extra fee. The target bucket must have this feature enabled. Select this checkbox if you want to use the data transfer acceleration service provided by Amazon. To learn more, refer to the Amazon S3 Transfer Acceleration.

Additional Advanced Options for Microsoft Azure

If your backup storage destination is Microsoft Azure, and you have the General Purpose v2 Azure account, select the required storage class (access tier).

The following options are available:

• Hot tier. An online tier optimized for storing data that is accessed or modified frequently. The hot tier has the highest storage costs, but the lowest access costs.
• Cool tier. An online tier optimized for storing data that is infrequently accessed or modified. Data in the cool tier should be stored for a minimum of 30 days. The cool tier has lower storage costs and higher access costs compared to the hot tier.
• Cold tier.An online tier optimized for storing data that is rarely accessed or modified, but still requires fast retrieval. Data in the cold tier should be stored for a minimum of 90 days. The cold tier has lower storage costs and higher access costs compared to the cool tier.
• Archive tier. An offline tier optimized for storing data that is rarely accessed, and that has flexible latency requirements, on the order of hours. Data in the archive tier should be stored for a minimum of 180 days.

To learn more about the difference between Azure storage tiers, refer to the Azure Blob Storage - Hot, cool,cold, and archive storage tiers article at docs.microsoft.com.

Click Next . | Top |

Select MS SQL Server Instance

Select MS SQL Server instance and the authentication method. You can choose either SQL Server or Windows authentication. After selection of preferable authentication method you will be asked to enter your username and a password.

Select Databases

Select databases to back up. Option list allows you to choose whether you need to backup all your databases, only user databases or to select a specific database manually.

Compression and Encryption Options

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. If you use MS SQL Server 2008 or higher, compression will be performed by SQL Server. If not, compression can be performed by the backup software.

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.

If you choose to save your backup plan configuration to the destination storage, be aware that the encryption password is not stored in the configuration file for security reasons. Ensure that you save this password securely, as it will be required to restore the backup's contents.

Click Next.

Retention Policy

Specify the retention policy settings for the backup plan. To read more about retention, refer to the Retention Policy chapter.

The following settings are available:

• Use the default retention policy. Select this option to use the predefined retention policy settings. To view and edit them, click the retention link in the option.
• Specify custom retention policy for backup plan. Select this option to use custom retention settings for this backup plan
  • Delete versions older than. Select this checkbox to delete old file versions in cloud storage, then specify the period and the criteria of this action applying: whether the counting starts from a modification date of an object or the last backup date
  • Keep number of full backups. Select this checkbox to specify the number of full backups that are kept in backup storage, then, in the Number of versions field, specify the value.

Click Next.

Schedule

Specify the schedule settings for the backup plan.

Then you will have to specify schedule options. You can run a backup plan manually or can setup the application to backup your files automatically. Program can be set on automatic backup at a certain time on a schedule or at user-specified intervals. User can choose whether to use predefined templates or configure advanced schedule.

If you have selected **Recurring (predefined templates), on the next step you will need to specify the start date and select simple schedule template.

If you have selected **Recurring (advanced schedule), on the next step you will be asked to set options for full backup, differential backup and transaction log backup. Here you can set date, time and frequency for each backup type. Also on this step yo will have to define the start date to run your first backup.

| Top |

Pre / Post Actions

Specify 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 Pre-backup action checkbox, then, in the field below, specify the path to the script to be executed before the backup plan run
• Specify the conditions of the script's execution:
  • Do not run the backup plan if pre-backup action fails option if you do not want the backup plan to run if the pre-backup script fails
  • Select Run the backup plan if pre-backup action fails option if you want the backup plan to launched regardless of the pre-backup script execution result
• To specify the action that will be performed after the backup plan is completed, select Post-backup action checkbox, then, in the field below, specify the path to the script to be executed after the backup plan terminates
  • Select Execute post-backup action if backup plan completes successfully option if you want to run it only if the backup plan is successfully completed
  • Select Execute post-backup action 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 Backup chain checkbox, then select the 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 has been successfully completed
  • Select Run the selected plan regardless of the backup plan completion result option if you want to run the chained backup plan regardless of the current backup plan execution results.
  • In case you chained a backup plan, you can enable the full backup for it regardless of the contents in the backup storage. To do this, select the Force full backup for the chained plan. By default, full or incremental backups will be started according to the schedule of this backup plan.

Click Next.

| Top |

Notifications and Logging

You can receive email notifications informing you about the backup process results, and maintain logging.

You can receive email notifications after each run of the backup plan or only in case it fails for some reason.

In addition, you can register the activity related to the backup routine in the Windows Event Log. You can choose whether to log all activity, or add new entries to the log only when a backup routine fails.

Summary

Review the configuration of the backup plan.

Click Next to proceed to the last wizard page.

Select the Run backup now checkbox to execute the backup plan immediately.

If you want to run the backup plan later, click Finish.

| Top |

Edit a Backup Plan

To edit the backup plan, proceed as follows:

1. Switch to the Backup Plans tab.
2. Open the backup plan you want to edit by expanding it, then click Edit.
3. Proceed through the Backup Wizard steps to adjust settings as needed.
4. If the plan is currently running, you'll need to select one of the following options:
   • Apply changes after the backup plan completes
   • Stop the plan and apply changes.
5. After choosing the desired option for the running backup plan, click Apply.
6. Save the edited plan configuration.

Delete a Backup Plan

To delete the backup plan, proceed as follows:

1. Switch to the Backup Plans tab.
2. Open the plan to delete, expand it, then click Delete.
3. Confirm the backup plan deletion.

| Top |