Skip to main content
Configure MS-SQL instance or AG for backup
Updated over a week ago

Enterprise Workloads Editions: βœ… Business | βœ… Enterprise | βœ… Elite

In Hybrid Workloads agent versions 4.7.1 and later, after registering an MS-SQL server, the Hybrid Workloads agent discovers standalone instances and availability groups (AG) running on the server. Druva lists these instances and AGs under the All SQL Resources page under Protect > MS-SQL servers. In Hybrid Workloads agent versions 4.8.5 and later, right after registering an MS-SQL server, Druva initiates a database discovery. If the database discovery fails to find any databases on the instance or AG, you cannot create backup sets for that instance or AG.

To back up MS-SQL databases in instances or AGs, configure the instances or AGs by creating MS-SQL backup sets.

This topic explains how to:


πŸ“ Note
​ You cannot select multiple SQL resources to create an MS-SQL backup set.


Configure MS-SQL standalone instance for backup

The following steps describe the procedure to configure MS-SQL server databases on a standalone instance for backup. Druva excludes AG databases from backups of standalone instances.

  1. Log in to the Management Console.

  2. Select the workload from the Protect menu. Note that if the All Organizations menu is enabled, you have to first select an organization that has the MS-SQL server instance and then select the workload.

  3. On the All SQL Resources page, select the standalone instance to be backed up, and click Create Backup Set.
    ​


    πŸ“ Note
    ​ You can only select one resource at a time to create a backup set.


  4. In the Backup Settings page, perform the following tasks:

    1. The Create Backup Set page displays the option to select or create an administrative group if no instances or availability groups on this SQL server have been associated with any administrative group.
      ​

      Backup Settings.png


      ​
      Select an administrative group from the drop-down next to Administrative Group. Druva gives you a Default Administrative Group that you can select. This group is not associated with any servers by default. You can also choose to create a new administrative group. To create a new administrative group, click New Administrative Group from the drop-down, and enter the following details:
      ​

      Create Administrative Group.png
      1. Name: Enter the name of the administrative group.

      2. Description: Enter a description of the administrative group. This is an optional field.
        Click Save. The newly created administrative group is now selected under the Administrative Group drop-down.

    2. In the Backup Settings section, perform the following tasks:
      ​

      New Backup Policy.png
      1. Enter a backup set name.

      2. Select a storage.
        ​Note: Storage once assigned cannot be changed later.

      3. Select a backup policy. You can click View Policy to view details of a selected policy. You can also click New Backup Policy from the drop-down to create a new backup policy. For more information on creating a new backup policy, see Create an MS-SQL server backup policy.

      4. Click Next.

  5. On the Backup Content page, perform the following tasks:

    Step 6.png
    1. Database Selection : Under the Database Name column, select the databases that you want to backup. All the databases are selected by default. You can also search for databases on this instance using the Search field. The database search is case-insensitive. If the database name is empdb and you enter EmpDb, Druva will search and list empdb as a choice.
      ​


      πŸ“ Notes

      • Databases that are part of an AG are auto-excluded from the standalone instance backup.

      • Databases that have already been configured for backup in other backup sets of the standalone instance are greyed out and cannot be selected.


    2. Include Patterns: Enter a database name or substring to include the database in the backup set. Selecting the Include all unconfigured and future databases checkbox includes all unconfigured databases that currently exist on the instance and automatically includes those that will be added in the future. If you select the Include all unconfigured and future databases checkbox, you need not select any databases manually.
      ​

      Druva does not support patterns like Emp*, *Emp, or *Emp*.

      For example, the pattern Emp includes all databases with the substring Emp in their names. If there are three databases on the instance with the names HREmployees, FinanceEmployees, and EngineeringEmployees, adding the pattern Emp includes all the three databases in the backup set.

      The include patterns are case-sensitive. The pattern Emp will include ITEmployees but exclude the database Exemployees in the backup. Druva only includes those databases that have the specified case-sensitive string in their database names. You can enter more than one substring.

    3. Exclude Patterns: Enter a database name or sub-string to exclude the database(s) from the backup set. For example, if there are two databases on the instance with the names FormerSystems and FormerSoftware, adding the substring Former in this field excludes both databases from the backup set.
      ​
      ​


      ❗ Important
      The text inputs in this field are case-sensitive. If you enter Former, Druva excludes databases with the substring in their names and if the substring matches the case of the text you enter. For example, Druva excludes the FormerSystems database, but Druva skips excluding the database formerproducts from the backup set. You can enter more than one substring.

      The exclusion filter takes precedence over the inclusion filter. For example, consider a database on the instance called FormerEmployees. You enter the pattern Former in both the Include and Exclude Pattern fields. The FormerEmployees database should be included based on the inclusion filter, however, it should be excluded based on the exclusion filter. In such a case, Druva excludes the database from the backup set.


    πŸ“ Note
    ​ Druva automatically includes or excludes any databases added in the future that match the Include or Exclude patterns in the backup set .
    Note :Databases that match the Exclude Pattern are greyed out and disabled in the Database Selection. Databases that match the Include Pattern are selected and disabled in the Database Selection.

    ​

  6. Click Next. The Configuration Pre-Checks screen is displayed with the following validations:
    ​

    Config Prechecks.png


    ​
    To prevent any future failures in your backups due to misconfigured settings, Druva broadly performs these validations while creating a backup set:

    • Client activation status: This validation ensures that the Druva agent is installed and activated on the SQL server.

    • Client connection status: This validation ensures that the client is in the connected state i.e. the Druva Agent Client Service on the SQL-Server is running.

    • Sysadmin privileges for the user account: Transaction Log and VDI differential backups require the assigned SQL server user login to have the β€˜Sysadmin’ role. This validation ensures that the same is provided.

    • Database mirroring check: Druva supports the backup of mirrored databases with some limitations. For more information, see System Requirements for MS-SQL servers.

    • Database recovery model check: Databases in the Simple recovery model do not support Transaction Log backups.
      If the backup set is configured for Transaction Log backups, this validation ensures that at least one of the selected databases is in the Full recovery model.

    If a check to create a backup set fails, then fix the issue and re-run the checks. If all the checks are successful, then the Summary screen is displayed.
    If a check fails and you still want to proceed, then click Proceed Anyway.

  7. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.
    ​

    Summary tab.png

πŸ“ Note
​ If no databases are manually selected or match the Include or Exclude Patterns, the backup of the backup set is skipped. If a database discovery that runs every 24 hours discovers any databases during a subsequent cycle, the backup is triggered as scheduled in the backup policy.The Hybrid Workloads agent backs up data from the instance based on the backup schedule and retains recovery points as defined in the retention period. The instance configured for backup now appears under the All SQL Resources page under Protect > MS-SQL servers.


Configure MS-SQL availability group (AG) for backup

Before you configure MS-SQL Availability Group (AG) for backup, ensure that the Hybrid Workloads agent is installed on all the nodes of the AG. During the backup of an AG, Druva excludes the standalone instance databases from the backup.

Prerequisites

Before configuring the MS-SQL AG for backup, ensure the following:

  1. Install the Hybrid Workloads agent on primary and all secondary nodes in the AG. If the Hybrid Workloads agent is not installed on the primary node of the availability group, Druva does not list details of all nodes of the AG on the Management Console.

  2. Enable the Readable secondary option for the secondary nodes for successful backups from that node.

  3. All the nodes in an AG must have the same Hybrid Workloads agent version installed on them.

Procedure

  1. Log in to the Management Console.

  2. Select the workload from the Protect menu. Note that if the All Organizations menu is enabled, you have to first select an organization that has the MS-SQL server availability group and then select the workload.

  3. On the All SQL Resources page, select the availability group to be backed up, and click Create Backup Set.
    ​


    πŸ“ Note
    ​ You can only select one availability group at a time to create a backup set.



    ​

  4. In the Backup Settings page, perform the following tasks:

    1. The Create Backup Set page displays the option to select or create an administrative group if this availability group has not been associated with any administrative group. Select an administrative group from the drop-down next to Administrative Group. Druva gives you a Default Administrative Group that you can select. This group is not associated with any servers by default. You can also choose to create a new administrative group. To create a new administrative group, click New Administrative Group from the drop-down and enter the following details:
      ​

      Backup Settings.png
      1. Name: Type the name of the administrative group.

      2. Description: Enter a description of the administrative group. This is an optional field.
        Click Save. The newly created administrative group is now selected under the Administrative Group drop-down.

    2. In the Backup Settings section, perform the following tasks:

      1. Backup Set Name: Enter a backup set name.

      2. Storage: Select a storage.


        πŸ“ Note
        ​ Storage once assigned cannot be changed later.


      3. Timezone: The timezone in which the backups must run. For example, if your timezone is US/Pacific, and you’ve scheduled Full backups for 5 AM every Sunday, the Full backup will run at 5 AM Pacific time in the US
        ​


        πŸ“ Note
        ​Timezone once assigned cannot be changed.



        ​

  5. Backup Policy: Select a backup policy. You can view details of a selected policy. You can also click New Backup Policy from the drop-down to create a new backup policy. For more information on creating a new backup policy, see Create an MS-SQL server backup policy.

  6. Click Next.

  7. On the Backup Content page, perform the following tasks:

    Backup Content.png
    1. Database Selection: Under the Database Name column, select the databases that you want to backup. All the databases are selected by default. You can also search for databases on the availability group using the Search field. The database search is case-insensitive. If the database name is empdb and you enter EmpDb, Druva will search and list empdb as a choice.


      πŸ“ Note
      Databases that have already been configured for backup in other backup sets of the availability group are greyed out and cannot be selected.


    2. Include Patterns: Enter a database name or substring to include the database in the backup set. Selecting the Include all unconfigured and future databases checkbox includes all unconfigured databases that currently exist on the instance and automatically includes those that will be added in the future. If you select the Include all unconfigured and future databases checkbox, you need not select any databases manually.

      Druva does not support patterns like Emp*, *Emp or *Emp*.

      For example, the pattern Emp includes all databases with the substring Emp in their names. If there are three databases on the instance with the names HREmployees, FinanceEmployees, and EngineeringEmployees, adding the pattern Emp includes all the three databases in the backup set.

      The include patterns are case-sensitive. The pattern Emp will include ITEmployees but exclude the database Exemployees in the backup. Druva only includes those databases that have the specified case-sensitive string in their database names. You can enter more than one substring.

    3. Exclude Patterns: Enter a database name or sub-string to exclude the database(s) from the backup set. For example, if there are two databases on the instance with names FormerSystems and FormerSoftware, adding the substring Former in this field excludes both the databases from the backup set.


      πŸ“ Note
      The text inputs in this field are case-sensitive. If you enter Former, Druva excludes databases with the substring in their names and if the substring matches the case of the text you enter. For example, Druva excludes the FormerSystems database, but Druva skips excluding the database formerproducts from the backup set. You can enter more than one substring.



      ​
      ​


      ❗Important
      The exclusion filter takes precedence over the inclusion filter. For example, consider a database on the instance called FormerEmployees. You enter the pattern Former in both the Include and Exclude Pattern fields. The FormerEmployees database should be included based on the inclusion filter, however, it should be excluded based on the exclusion filter. In such a case, Druva excludes the database from the backup set.



​
​


πŸ“ Notes

  • Druva automatically includes or excludes any databases added in the future that match the Include or Exclude patterns in the backup set .

  • Databases that match the Exclude Pattern are greyed out and disabled in the Database Selection. Databases that match the Include Pattern are selected and disabled in the Database Selection.


  1. Click Next.

  2. The Configuration Pre-Checks window is displayed with the following validations:
    ​

    Prechecks.png


    ​
    To prevent any future failures in your backups due to misconfigured settings, Druva performs these validations while creating a backup set:

    • Client activation status:This validation ensures that the Druva agent is installed and activated on all nodes of the Availability Group.

    • Client connection status:This validation ensures that the client is in the connected state i.e the Druva Agent Client Service on the SQL-Server is in a running state.

    • Sysadmin privileges for the user account:Transaction Log and VDI differential backups require the assigned SQL server user login to have the β€˜Sysadmin’ role. This validation ensures that the same is provided.

    If a check to create a backup set fails, then fix the issue and re-run the checks. If all the checks are successful, then the Summary screen is displayed.
    If a check fails and you still want to proceed, then click Proceed Anyway.

  3. In the Summary screen, verify the backup set configuration settings and the databases selected for backup and click Finish.
    ​

    Summary.png

πŸ“ Note
​ If no databases are manually selected or match the Include or Exclude Patterns, the backup of the backup set is skipped. If a database discovery that runs every 24 hours discovers any databases during a subsequent cycle, the backup is triggered as scheduled in the backup policy


The Hybrid Workloads agent backs up data from the availability group based on the backup schedule and retains recovery points as defined in the retention period. The availability group configured for backup now appears under the All SQL Resources page under Protect > MS-SQL servers.

Next steps

The SQL Backup Sets page shows the backup sets for the Standalone Instance and Availability Groups. Druva backs up the databases on the configured standalone instances and availability groups as per the configured schedule.


πŸ“ Note
​ After adding a database to an MS-SQL server, ensure that you update the Include Patterns in the respective backup set with the appropriate substrings or manually include the database for backup.


If a restore is required, you can trigger it from the Management Console. For more information about restore jobs, see:

If you want to modify an administrative group, or backup policy, or upgrade the server, see:

Similarly, you can add file backup sets to the server. For more information about adding a file backup set to a Windows Server, see Add file backup set to the configured server. If you have deployed CloudCache, you can attach backup sets to the CloudCache. For more information, see Attach a new backup set to CloudCache.

Did this answer your question?