Manage automated backups (preview) | SQL Server enabled by Azure Arc
Applies to:
SQL Server
The Azure extension for SQL Server can perform backups automatically to local storage or network shares. Backups are written to the default backup location for the SQL Server enabled by Azure Arc instance.
This article explains how you can:
- Enable 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.
You can enable automated backups through Azure portal or via az
CLI.
To enable automated backups, set the retention days to a nonzero value.
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
- Instance level
- Database level (available from extension version 1.1.2594.118 or later)
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
Beginning with the August 2024 release, any extensions deployed via auto-onboarding will automatically have the least privilege configuration.
Existing servers with extension 1.1.2717.190
(June 2024) or later will have least privileged configuration automatically applied. This application will happen gradually.
To prevent automatic application of least privilege, block extension upgrades to version 1.1.2717.190
or later.
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.
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
Add
[NT AUTHORITY\SYSTEM]
account to Users, and make it a member of the db_backupoperator role inmaster
,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
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:
- Disable any existing external backup routines.
- Browse to the SQL Server you want to enable automated backups.
- Select Backups.
- Select Configure policies.
- 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.
- 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:
- Select the instance
- Select the database
- Under Data management on the left
- Select Backup (preview) - Configure database backup policies (Preview)
- Select Configure policies.
- 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.
- 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:- Connect to the Arc-enabled SQL Server from SSMS.
- 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
- Automatic backup to URL is not currently available.
- 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.
Related tasks
Feedback
https://aka.ms/ContentUserFeedback.
Binnenkort beschikbaar: In de loop van 2024 zullen we GitHub-problemen geleidelijk uitfaseren als het feedbackmechanisme voor inhoud en deze vervangen door een nieuw feedbacksysteem. Zie voor meer informatie:Feedback verzenden en weergeven voor