MS SQL Server Backup
Before taking database backup for your MS SQL Server database server, a lightweight agent needs to be installed on the database server, please refer to Agents for how to install agents on your database servers.
After the agents had been installed and licensed for database backup, please follow the below steps to create database backup job.
Configure Application
After the installation of Vinchin physical backup agent on SQL Server database nodes, users have to license the agent with the database backup module.
When done with installation and licensing, please open Vinchin Backup Server web console and go to Resources > Agents page, find the target agent, click on Options and then select Application to configure application settings for database backup.
Click on the Configure Application button to configure the application settings.
In the Application Type dropdown list, please select SQL Server. Select the SQL Server instance and click on Next.
There are two authentication modes, Windows Authentication and SQL Server Authentication. If customers want to backup MS SQL failover cluster, please select the SQL Server Authentication to configure the application settings.
If select Windows Authentication, agent will use the user which you logged in to connect to the SQL Server database, when running database backup.
If select SQL Server Authentication, please fill in the username and password used to log in to the database you want to back up. For MS SQL failover cluster, please enable the Failover Cluster option. Fill the Cluster IP address of MS SQL cluster. Select other database nodes to connect the database cluster.
When SQL Server application is successfully configured, in the agents list, you can see the application information. Now you are able to create backup jobs for the SQL Server database server.
Before backing up MS SQL Server database
Check the Backup Services Log On Account
If you want to use Windows Authentication mode to configure the database for backing up, please edit the database backup services to log on as administrator from Windows Services. Please first stop the "database_backup_service" and "database_transfer_service" services by right click on them and select Stop.
When these 2 services have been stopped, right-click on them and select Properties > Log On, select log on as this account, then fill in the administrator account name and password, click on Apply and restart the backup services.
Check Database User Permission
If select SQL Server Authentication mode to configure application. Please check the database user permissions for which you wish to perform SQL Server database backup, at least ensure the user has sysadmin permission, check the details below.
Check Recovery Model
Check the recovery model of database, please ensure the recovery mode is “Full”, otherwise log backup job will fail.
Create Backup Job
To create SQL Server database backup jobs, please go to Physical Backup > Database Backup > Backup page. There are 4 steps to create a database backup job.
Step 1: Backup Source
First select database backup agent from left column, then expend SQL Server instance and select the databases which need to be backed up.
Step 2: Backup Destination
A backup destination (backup storage) should be associated with this backup job.
In the Target Node dropdown list, you can select a backup node on which you want the backup data to be processed and stored.
In the Target Storage dropdown list, the storages belong to the selected backup node can be selected. When done selecting the backup storage, please click on Next button to continue.
Step 3: Backup Strategies
In the General Strategy it including Schedule, Throttling Policy, Data Storage Policy and Retention Policy.
In the Schedule field, you can configure the time schedule of the backup job, you can configure the job as a Backup as Scheduled job or a Once-off Backup job.
For a once-off backup job, the job will only run for once, and only full backup will be performed. You only have to appoint a time of when to start the backup job, in the Time Schedule field.
For a backup as scheduled job, you can schedule Full Backup, Differential Backup and Log Backup. Here we take these three Backup as an example. Please set the backup mode and backup schedule as per your actual demands, then please click on Next to continue.
Throttling Policyr is optional. It can be used to limit the transmission speed during database backup if needed.
There are 3 options in Data Storage Policy section, Data Deduplication, Data Compression and Data Encryption. By enabling these 3 options, the backup data will be deduplicated, compressed and encrypted before writing into backup storage.
For the retention policy of the database backup, there are 2 retention mode, retain the database backups according to Number of Restore Points or Number of Days.
For the retention mode Number of Restore Points, the restore points will be counted by full restore points, including the differential backups and log backups dependent on this full backup.
For retention mode Number of Days, Vinchin Backup Server will save the restore points within the specified number of days.
When the retention policy is triggered, the outdated restore points will be purged to comply with the retention policy.
In the transmission Strategy, you can choose to enable Encrypted Transmission for data safety. The backup data will be transferred through LAN by default.
Transmission Network can be selected to connected the target database server.
Advanced Strategy including Check Database Integrity, SQL Server Compression and Page Checksum.
Check Database Integrity function is check database integrity and physical errors before the database backup job start.
SQL Server Compression is provided by SQL Server to reduce data transfer, data backup time and saves backup storage.
Page Checksum is used to verify the backup data during the transmission to avoid data damage.
Notice
Between General Strategy Compressed Transfer and Advanced Strategy SQL Server Compression prefer only enable Compressed Transfer in general strategy. SQL Server Compression will use more CPU and memories.
Step 4: Review & Confirm
After completing the above mentioned settings, you are able to review and confirm the settings in one screen.
A job name can be specified for identification of the database backup job, and by clicking on the Submit button to create the backup job.