Configuring SQL Database Backup
Step 1: Adding Basic Job Details
Enter the following details;
Backup Job Name: Provide any proper name for your backup job.
Backup Repository: Select the backup repository type. You can choose either Block Storage or Object Storage.
Repository Name: Once selecting the repository type, use the drop-down list to select the repository for your backup job.
Immutable Backups (Only for Object Storage):
- You have the option to enable this feature by checking the box, which will secure your backups as immutable for a specific duration. The duration can be specified and you do it for up to 999 day(s). The duration set will be applied to this backup job, which you’ll configure in subsequent steps. When configuring other backup jobs, ensure to adjust the duration according to your needs.
- Immutable backup in the BDRSuite Backup Server is a backup strategy or system you can use where once the data is backed up, it cannot be edited, deleted, or tampered with until a predefined time period expires that can be set up as shown below in the image representation. This approach adds an extra layer of protection to the backup data, making it resistant to ransomware attacks, accidental or malicious changes
Job Description: Provide a job description for future reference.
Step 2: Instance Selection
Select MS SQL Server: Select the Server which you want to configure backup for.
Select MS SQL Server Instance: Select from the list of MS SQL Server instance by clicking on the drop down menu.
Note: To ensure that the MS SQL Server instances are listed, it’s essential to confirm that the BDS (BDRSuite Delegation Services) are running with administrative privileges on the target host.
To manually add the instance you click on Add MSSQL Instance Server option. You can enter the instance name and save.
In the BDRSuite Backup Server you can proceed by selecting from the following two Authentication type.
- Use Windows Authentication: You can choose this option to authenticate using the Windows Host Credentials.
- Use MSSQL Server Authentication (Recommended): You can choose this type to authenticate using the MSSQL Server credentials. (Ensure that the provided credentials have administrative privileges. If the installed BBA/BDS (BDRSuite Delegation Service) is currently running on the Local System Account, modify it to operate with administrative privileges in the Services settings of your MS SQLServer machine).
MSSQL Database Credentials: Select the existing credentials or click on Add credentials to add a new one.
Backup Data Dump Location:
Enter the path to temporarily dump the backup data on the database server before initiating the backup process. By default the dump location selected at the time of adding data source will be used. If you want to customize the dump location for each backup job then you can do that by clicking on the ‘select location’ icon.
Step 3: Database Selection
The database selection begins by listing the added MSSQL databases. You can apply the filter based on the database recovery model list by clicking on the drop-down menu on the left-hand side of the page. After the selection is complete, the chosen databases for backup will be displayed in a table on the right-hand side of the page for your reference.
Select the required MSSQL Database to proceed to next step.
Step 4: Scheduling
The schedule option defines the frequency of your MSSQL Backup job.
Full Backup Schedule
You can configure the full backup schedules by choosing between Run One Time/Run Daily/Run Weekly/Run Monthly options.
- Select Run One Time option if you want to run your backup only for once.
- If you want to run your backup every day, select the Run Daily option and configure the period in which your backup has to occur.
- If you want to run your backup every week on a specific day and time, choose the Run Weekly option. Select the time frame and day in which you want to run the backup.
- If you want to run your backup using the monthly option then choose the months when ‘Full Backup’ should run.
Note: The recommended interval is to schedule full backups every 3 or 6 months.
Differential Backup Schedule
While performing the differential backup schedule, the BDRSuite Backup Server will only dump the changes of the database since the previous full backup.
Enable the differential backup schedule by toggling the slider to the right. Once enabled you will have to specify the frequency of the differential backup schedule. You can specify it to run on a every few minutes, hourly, daily, weekly or monthly basis.
Note: The recommended frequency is to schedule differential backups either on a weekly or monthly basis.
Transactional Log Backup
You can backup all the transactional logs since the previous backup schedule.
Enable the transactional log backup by toggling the slider to the right. Once enabled you will have to specify the frequency of the transactional log backup. You can specify it to run on a every few minutes, hourly, daily, weekly or monthly basis.
Note: The suggested frequency is to schedule transactional log backups every few hours or on a daily basis.
Advanced Schedule Settings
The Advanced Schedule Settings allow you to specify the Backup Window time and Backup Validity for your backup job.
Backup Window
If you don’t want the backup run at a specific time interval, then you can specify it in the backup time window. The backup window prevents the backup job from running during peak production hours and ensures there is no unwanted overhead in the production environment. Select one of the available options to set up the backup window for your backup job
Use Global Settings: Selecting the‘ Use Global Settings’ option will apply the Backup Window setting of your backup server to this backup job. You can set up the global backup window settings for your entire backup server at Backup > Configure Backup Settings > Backup Window.
In case, if you are selecting the ‘Use Global Settings’, but if global backup window settings are disabled for your backup server, then the job will run as per its schedule
If you are modifying the Global Backup Windows settings later, it will affect all the backup jobs that are selected with ‘Use Global Settings’
Use Custom Settings: On selecting ‘Use Custom settings’, you have to specify the start time and end time for the window. The backup job will not during the selected time interval. You can also specify the applicable days for the backup window settings such as Mon-Fri or All days. Selecting the ‘Use Custom settings’ will override the global backup window settings.
No Preferences: Selecting ‘No Preferences’ will override the global backup window settings and the backup job will run as per the schedule.
Backup Validity: Set the validity for your backup job by specifying the start date and the expiration date. The Backup job will start to run only after the start date.
If you want a backup job to start to run later, for example, 5 days later from the day you have created the backup job, then you can specify the start date of the backup job accordingly. The expiry date is optional for a backup job, if the expiry date is set the incremental backup or additional full backup does not proceed after that.
Backup Data Retention
No of full backups to Retain: Only the configured number of latest full backups will be retained. The oldest full backups and its associated differential/transactional log backup will be deleted.
No of differential backups to Retain: Only the configured number of latest differential backups will be retained
Encryption
You can encrypt all the backup data for an added protection. When encryption is enabled all the backup data blocks are encrypted using the AES-256 bit encryption algorithm both during the transfer and at the storage. For encrypting the backup data, you can use the system-generated encryption key or use a custom encryption key.
- By default, encryption is disabled for all the backups jobs, you can slide the toggle to the right to enable encryption for your backup data.
- Select ‘Use System Generated Encryption Key’ if you want to encrypt all your backup data using BDRSuite’s automatic System Generated Encryption Key. You do not need to specify any during the recovery or editing the backup job later.
- Select ‘ Use a Custom Encryption Key’ if you want to encrypt all your backup data using your own Encryption Key. You can select the already created encryption keys that are created for other backup jobs from the drop-down list or you can
- Add a new encryption key by providing the encryption password, encryption key hint.
- After providing the password and hint for the encryption key, click on the Save button.
Step 6: Review Configuration
- The last step in your backup process is to review the configurations you have selected. BDRSuite Backup Server supports creating multiple backup schedules each with its own configuration. The backup job name will uniquely identify the backup. While restoring your backup data, you need to choose the data to be restored using its schedule name.
- You have the option of running the backup job immediately after saving the backup. If you want your backup job to be triggered immediately after you save the backup, irrespective of the backup schedule you have configured, select the Run this job immediately after the saving option.
- Click Save the backup option once all the configurations are reviewed. On saving the backup, you will be prompted to confirm to proceed further. Click OK to complete the backup progress.
Related Articles
Configuring MySQL Database Backup
Step 1: Adding Basic Job Details Enter the following details; Backup Job Name: Provide any proper name for your backup job. Backup Repository: Select the backup repository type. You can choose either Block Storage or Object Storage. Repository Name: ...Configuring PostgreSQL Database Backups
Step 1: Adding Basic Job Details Enter the following details; Backup Job Name: Provide any proper name for your backup job. Backup Repository: Select the backup repository type. You can choose either Block Storage or Object Storage. Repository Name: ...Step-by-Step Guide: Restoring a Specific SQL Database from Image Backup for MS SQL Database Recovery
KB ID: 62 The need arises to restore a specific SQL Database from an Image Backup, specifically focusing on MS SQL Database recovery. Solution: Follow the steps below for the MS SQL DB Recovery: Step 1: Mount Vembu Virtual Drive and attach the VHD ...Troubleshooting: Getting an empty database list or database are not listing while creating SQL server backup
KB ID: 115908 Cause: The occurrence of an empty database list is often attributed to the user authenticated to the SQL instance lacking the necessary permissions. Solution: To address this issue, follow these steps to ensure the user has appropriate ...Add MS SQL Server
MS SQL Server can be backed up using the BDRSuite Backup Server. You can simply add the MS SQL Host as a Data Source to the BDRSuite Backup Server and configure backups. Navigate to Data Sources tab and choose Add MS SQL Server. When a MS SQL Server ...