In this article:
The idea of creating a database backup is to make a database dump to a file and include the backup plan for the folder in which it is located.
- In order to make a copy of the database, a backup plan of the file type or disk image should be created.
- In the Devices tab, select the server where the Microsoft SQL database is located. Choose the device by marking the checkbox next to the appropriate single device from the list. After selecting the device, choose "Apply" in the lower right corner.
- In the "Data to be secured" field, indicate the folder where the database dump included in the backup plan should to be located. For a disk image copy, indicate the disk on which the database dump will be performed. The indicated folder or the drive is selected by choosing an appropriate checkbox. The operations are confirmed with the buttons: "Save" and "Apply".
- In the next step we choose the backup storage and define the schedule.
- Go to the advanced settings.
- Select Pre-backup script and SQL Server database backup in the advanced settings of the backup plan.
7. Enter the name of the MS SQL server database user in the "Database user" field, i. e. the name that is used for the authorization in a database.
It is a good practice to create an additional user (not use ’sa’ account) that can be applied for non-administrative tasks, i. e. backup tasks.
- The next step will be to define a password for the authorization in a database. For this purpose, use a password manager implemented in the Xopero ONE. Select this item from the advanced settings by clicking in the appropriate place.
- From the open form, select “Add new or select password form”. Then complete the required information, i.e. the password name in the manager and the target password for authorization in the database.
After completing the data and confirming it with the "Save" button, the password will be automatically choosen. If this does not happen, select it from the list in the password manager.
- The next step is to define the script arguments.
The arguments are as follows:
full_only | full_diff server_name output_dir [database1] [database2] .. [databaseN]
full_only or full_diff - defines whether the script will always perform a full database dump, or a differential one with a periodic full backup.
Full backups of the database are made in accordance with the schedule established when a backup plan was being created, while differential backups are performed during the execution of an incremental or differential backup defined in the schedule.
When full and periodic incremental backups are performed, two files are kept in the directory with database dumps for one database - dump with a full backup and dump with a differential backup. Each subsequent differential copy does not create another dump - it overwrites the previous one.
If you remove a differential copy dump, another differential copy will be made with respect to the last full copy.
If the full backup is deleted, but the differential backup is not deleted, both copies will be turned into one full backup, and the next differential backup will be created during the next backup according to the schedule specified in the backup plan.
server_name - name of the MSSQL server instance,
output_dir - the directory where the database dumps will be placed. This directory was selected in step 3.
[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.
full_diff WINSRV-2019\SQLEXPRESS C:/msSqlBackup testToBackupDB
At this point, the creation of the backup plan for the Microsoft SQL database has been completed. After executing the plan, the database dump will be saved in the indicated folder and automatically saved in the storage defined in point 4.
- Select the options: “Wait for the script to continue the task” and “Fail the task if the script fails”.
If the Xopero ONE performs a differential database dump, and the user wants to perform an additional backup using other tools, it is possible only in the case when the tool allows you to make a copy in the COPY_ONLY mode.
After the backup plan is completed, the database dump will appear in the selected folder.
- In the devices tab, select the restore option for the device which the database is to be restored from.
- Choose a backup plan defined as MS SQL database backup
- Select the version that you want to restore, then select the button: Restore.
- Select the folder where the database dump was saved.
- Click the button: Restore selected.
- After that you need to indicate the directory which the dump of the database is to be restored to. You can restore it to the original directory or point to make a new one.
- Select the button: "Start now".
- At this step the selected data has been restored.
From this level, you can restore the database using Microsoft SQL Server Management Studio.
- In Microsoft SQL Management Studio, after logging in to the directory tree, right-click on "Databases" and select the "Restore Database" option.
- In the opened window, select "Device" as the backup source and indicate the path to the folder containing the database dump file * .bak
- After selecting the * .bak file, choose “OK”.
- Confirm with the "OK" button.
- The database was successfully restored and is listed in the directory tree.