Microsoft SQL database backup

In this article, you will get information on how to configure the Microsoft SQL database environment using Xopero ONE.

The database backup process in Xopero ONE can be split into two different operations:

Creating a database dump using backup scripts,
Performing a backup of the database dump.

The first operation is triggered through Xopero ONE using the Backup scripts feature, and the second action is simply a file-level/image-level backup plan performance done directly by Xopero ONE. This article describes how to use Backup scripts in order to create a Microsoft SQL database dump, so it can be included in a backup plan.

See also how to create a file-level backup and image-level backup plan using Xopero ONE

How to backup Microsoft SQL environment using Xopero ONE

To configure the backup Microsoft SQL database environment open the Plans tab and Add plan, then select the Advanced settings tab on the bottom.

Scroll down to the Backup scripts and click the pre-backup script.

Toggle the pre-backup script switch to ON and select the script dedicated for backup Microsoft SQL database environment.

Enter the SQL username in the "Database user" field.

It is good practice to create dedicated users responsible to perform database backup tasks.

Set the user password by adding it to the safe password manager or setting it from the list.

The user provided for the Database user field must use SQL Server authentication

See also how to add the password to the Safe Password Manager:

Add A New Password

With the next step, it is required to set up the script arguments.

Script arguments should be provided into Script arguments field this way:

copy_type server_name "output_dir" database1 database2 ... databaseN

where:

If your operating system runs other backup software or schedulers that perform additional database dumps, you should use full_only dumps. Using other types (full_diff and copy_only) may cause problems if other processes on the system also perform database dumps.

copy_type - parameter responsible for setting what copy type should be performed. The user can decide which of 3 copy types should be used: full_only, full_diff, and copy_only. - If full_only is selected always performed will be the full copy. - If full_diff is selected performed will be a differential copy with a periodic full copy. - Copy_only contains the entire database, but does not affect the regular backup cycle.
Full copies are performed according to the planned schedule. Differential copies are performing always when differential and incremental copies are planned on schedule.
If the file contained a differential copy will be deleted, and the next differential copy will be created respecting to last full copy made.
server_name - Microsoft SQL server name along with an instance.
output_dir - the directory where the database dumps will be placed, and which should also be selected as directory to backup.
database1 ... - names of databases (everything should be separated by a space) to be backed up. However, you can ignore this option - if no database is selected, Xopero ONE will automatically protect all databases from the server the user indicated in the database access parameters.

An example of the database script's arguments:

full_diff WINSRV-2019\SQLEXPRESS "C:\MsSQLDumps" database1 database2

Set two additional options by using the checkboxes - Wait for the script to continue the task, so the application will wait until the dump is ready and Fail the task if the script fails - so in case of script failure, the system will also notify about backup error.

After setting Save configuring backup for the Microsoft SQL environment will be finished.