Edit

Manage automated backups - SQL Server enabled by Azure Arc (preview)

  • Article

Applies to: SQL Server

The Azure extension for SQL Server can perform backups automatically for the system and user databases of the instance of SQL Server enabled by Azure Arc.

This article explains how you can:

  • Enable these built-in automated backups
  • Configure backup schedule

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

The latest updates are available in the Release notes - SQL Server enabled by Azure Arc.

Backup files are stored in the default backup location of the SQL instance.

You can enable automated backups through Azure portal or via az CLI.

To enable automated backups, set the retention days to a nonzero value.

Supported license types

Automated backups are only available for licenses with Software Assurance, SQL subscription, or pay-as-you-go. For details, see Feature availability depending on license type.

Backup frequency and retention days

You can configure two properties for automated backups:

  • retention days - number of days to retain the backup files. Use a number between 1 and 35. If the backup retention day is set to 0, automated backup is disabled and no backups are taken, even though backup policy is retained.
  • backup schedule - the schedule at which the full, differential, and transaction log backups should be performed. Depends on backup type:
    • Full backups: Daily or weekly
    • Differential backups: Every 12 hours or every 24 hours
    • Transaction log backups: Increments of 5 minutes.

You can also run backups on a default schedule:

  • Retention period: 7 days
  • Full backups: every 7 days
  • Differential backups: every 24 hours
  • Transaction log backups: every 5 minutes

Backup schedule level

You can schedule backups at

If both database and instance level backup schedule is set, database level schedule takes precedence over the instance level backup schedule. If you delete the database level backup schedule, the instance level backup schedule applies.

Assign permissions

The backup service within the Azure extension for Arc-enabled SQL Server uses [NT AUTHORITY\SYSTEM] account to perform the backups. If you're operating SQL Server enabled by Arc with least privilege, A local Windows account - [NT Service\SQLServerExtension] - performs the backup.

Note

If the Azure Extension is version 1.1.2594.118 (February 2024 release) or later, least privilege mode will be automatically enabled in the coming months.

If you use Azure extension for SQL Server version 1.1.2504.99 or later, the necessary permissions are granted to [NT AUTHORITY\SYSTEM] automatically. You don't need to assign permissions manually.

For earlier extensions only, follow the below steps to assign permission to [NT AUTHORITY\SYSTEM] account.

Note

This requirement applies to the preview release.

  1. Add [NT AUTHORITY\SYSTEM] account to Logins, and make it a member of the dbcreator server role at the server level. Run the following Transact-SQL to add this account:

    USE master;
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [NT AUTHORITY\SYSTEM];
GO

  2. Add [NT AUTHORITY\SYSTEM] account to Users, and make it a member of the db_backupoperator role in master, model, msdb, and each user database.

    For example:

    CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM];
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [NT AUTHORITY\SYSTEM];
GO

  3. Run the preceding code for each user and system database (except tempdb).

Configure automated backups

Automated backups are disabled by default.

After you assigned permissions, you can schedule automated backups. After the automated backups are configured, the Arc SQL extension initiates a backup to the default backup location.

The backups are native SQL Server backups, so all backup history is available in the backup related tables in the msdb database.

Instance level

To enable automated backups in Azure portal:

  1. Disable any existing external backup routines.
  2. Browse to the SQL Server you want to enable automated backups.
  3. Select Backups.
  4. Select Configure policies.
  5. Under Configure policies:
    • Set a value for backup retention days - between 1 and 35.
    • Set a schedule for the full, differential, and transactional log backups.
  6. Select Apply to enable this configuration.

Set retention period and frequency to meet business requirements. The retention policy should be greater than the full backup frequency. As a measure of safety, the automated backup process always keeps backups sets of at least one full backup frequency plus the retention days.

Database level

To configure individual custom database level backup in the portal:

  1. Select the instance
  2. Select the database
  3. Under Data management on the left
  4. Select Backup (preview) - Configure database backup policies (Preview)
  5. Select Configure policies.
  6. Under Configure policies:
    • Set a value for backup retention days - between 1 and 35.
    • Set a schedule for the full, differential, and transactional log backups.
  7. Select Apply to enable this configuration.

Set retention period and frequency to meet business requirements. The retention policy should be greater than the full backup frequency. As a measure of safety, the automated backup process always keeps backups sets of at least one full backup frequency plus the retention days.

Disable automated backup

If the backup retention day is set to 0, automated backup is disabled and no backups are taken, even though backup policy is retained. Setting the backup retention to a nonzero value enables the policy again.

This setting applies to both database and instance level backup. If database level backup schedule is disabled, no backups are taken for the database even if instance level backup is scheduled.

Delete automated backup

From the portal for individual database level backup scheduling page, select Revert backup policy to instance level to delete the database level backup policy.

To delete instance level backup schedule, you can do it through CLI. Once deleted, no backup is taken either in instance level or database level. You must configure a new backup schedule to take the backup again.

Delete Instance Level Policy

az sql server-arc backups-policy delete --name <arc-server-name> --resource-group <resourcegroup>

Example:

az sql server-arc backups-policy delete --name MyArcServer_SQLServerPROD --resource-group MyResourceGroup

Delete Database Level Policy

az sql db-arc backups-policy delete --name <sql-database-name> --server <arc-server-name> --resource-group <resourcegroup>

Example:

az sql db-arc backups-policy delete --name MyDatabaseName --server MyArcServer_SQLServerPROD --resource-group MyResourceGroup

View current backup policy

To view the current backup policy for a SQL Server, run the following command:

az sql server-arc backups-policy show --name <arc-server-name> --resource-group <resourcegroup>

Example:

az sql server-arc backups-policy show --name MyArcServer_SQLServerPROD --resource-group MyResourceGroup

Output:

{
  "differentialBackupHours": 24,
  "fullBackupDays": 7,
  "instanceName": "MSSQLSERVER01",
  "retentionPeriodDays": 16,
  "transactionLogBackupMinutes": 5
}

Backup system databases

When the built-in automated backups are enabled on an instance of SQL Server enabled by Azure Arc, the system databases are also backed up into the default backup location. Only full backups are performed for the system databases.

Considerations

  • The backup files are stored at the default backup location as configured at the SQL Server instance level.

  • To find the default backup location for a SQL Server instance (on SQL Server 2019 (15.x) and later), run:

    SELECT SERVERPROPERTY('InstanceDefaultBackupPath');

  • For SQL Server versions below 2019, the default backup path is stored in a registry setting. Configure this setting with the extended stored procedure xp_instance_regwrite or from SQL Server Management Studio (SSMS). To use SSMS:

    1. Connect to the Arc-enabled SQL Server from SSMS.
    2. Go to Server properties > Database Settings > Database default locations.

  • The backup policy configured at the instance level applies to all the databases on the instance.

  • If both database and instance level backup schedules are set, database level takes precedence over the instance level backup schedule. Deleting the database level backup schedule reverts back to instance level backup schedule, if there's any.

  • The value for --name should be the name of the SQL Server enabled by Azure Arc, which is usually in the [Servername_SQLservername] format.

  • The value for --retention-days can be from 0-35.

  • A value of 0 for --retention-days indicates to not perform automated backups for the instance or the database.

  • If there are multiple SQL Server instances on the same host where the Azure extension for SQL Server is installed, you need to configure automated backups separately for each instance.

  • If you change the --retention-days after the --backups-policy is already configured, any change takes effect going forward and isn't retroactively applied.

Limitations

  • The user databases need to be in full recovery model for the backups to be performed. Databases that aren't in full recovery model aren't automatically backed up.
  • Automated backups are currently not supported for Always On failover cluster instances (FCI).
  • Automated backups aren't supported on any instance that hosts an availability group (AG) replica.
  • Automated backups are only available for licenses with Software Assurance, SQL subscription, or pay-as-you-go. For details, see Feature availability depending on license type.