With Azure SQL Database, you can set a long-term backup retention (LTR) policy to automatically retain backups in separate Azure Blob storage containers for up to 10 years. You can then recover a database using these backups using the Azure portal, Azure CLI, or PowerShell.
If you prefer to run CLI reference commands locally, install the Azure CLI. If you're running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.
If you're using a local installation, sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For other sign-in options, see Sign in with the Azure CLI.
When you're prompted, install the Azure CLI extension on first use. For more information about extensions, see Use extensions with the Azure CLI.
Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.
Prepare your environment for PowerShell.
Note
This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
Important
The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.
For Get-AzSqlDatabaseLongTermRetentionBackup and Restore-AzSqlDatabase, you need to be a member of one of the following roles:
The SQL Server Contributor role does not have permission to delete LTR backups.
Azure role-based access control (RBAC) permissions could be granted in either subscription or resource group scope. However, to access LTR backups that belong to a dropped server, this permission must be granted in the subscription scope of that server:
You can configure SQL Database to retain automated backups for a period longer than the retention period for your service tier.
In the Azure portal, navigate to your server and then select Backups. Select the Retention policies tab to modify your backup retention settings.
On the Retention policies tab, select the database(s) on which you want to set or modify long-term backup retention policies. Unselected databases will not be affected.
In the Configure policies pane, specify your desired retention period for weekly, monthly, or yearly backups. Choose a retention period of '0' to indicate that no long-term backup retention should be set.
Select Apply to apply the chosen retention settings to all selected databases.
Important
When you enable a long-term backup retention policy, it may take up to 7 days for the first backup to become visible and available to restore. For details of the LTR backup cadence, see long-term backup retention.
Run the az sql db ltr-policy set command to create an LTR policy. The following example sets a long-term retention policy for 12 weeks for the weekly backup.
az sql db ltr-policy set \
--resource-group mygroup \
--server myserver \
--name mydb \
--weekly-retention "P12W"
This example sets a retention policy for 12 weeks for the weekly backup, 5 years for the yearly backup, and the week of April 15 in which to take the yearly LTR backup.
az sql db ltr-policy set \
--resource-group mygroup \
--server myserver \
--name mydb \
--weekly-retention "P12W" \
--yearly-retention "P5Y" \
--week-of-year 16
# get the SQL server
$subId = "<subscriptionId>"
$serverName = "<serverName>"
$resourceGroup = "<resourceGroupName>"
$dbName = "<databaseName>"
Connect-AzAccount -Environment AzureChinaCloud
Select-AzSubscription -SubscriptionId $subId
$server = Get-AzSqlServer -ServerName $serverName -ResourceGroupName $resourceGroup
# create LTR policy with WeeklyRetention = 12 weeks. MonthlyRetention and YearlyRetention = 0 by default.
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName `
-ResourceGroupName $resourceGroup -WeeklyRetention P12W
# create LTR policy with WeeklyRetention = 12 weeks, YearlyRetention = 5 years and WeekOfYear = 16 (week of April 15). MonthlyRetention = 0 by default.
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName `
-ResourceGroupName $resourceGroup -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16
View backups and restore from a backup
View the backups that are retained for a specific database with an LTR policy, and restore from those backups.
In the Azure portal, navigate to your server and then select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane appears with a list of the available LTR backups for the selected database.
In the Available LTR backups pane that appears, review the available backups. Select a backup to restore from.
To restore from an available LTR backup, select the backup from which you want to restore, and then select Restore.
Choose a name for your new database, then select Review + Create to review the details of your Restore. Select Create to restore your database from the chosen backup.
On the toolbar, select the notification icon to view the status of the restore job.
When the restore job is completed, open the SQL databases page to view the newly restored database.
Run the az sql db ltr-policy show command to view the LTR policy for a single database on your server.
az sql db ltr-policy show \
--resource-group mygroup \
--server myserver \
--name mydb
View LTR backups
Use the az sql db ltr-backup list command to list the LTR backups for a database. You can use this command to find the name parameter for use in other commands.
az sql db ltr-backup list \
--location chinaeast2 \
--server myserver \
--database mydb
az sql db ltr-backup restore \
--dest-database targetdb \
--dest-server myserver \
--dest-resource-group mygroup \
--backup-id $get_backup_id
Important
To restore from an LTR backup after the server or resource group has been deleted, you must have permissions scoped to the server's subscription and that subscription must be active. You must also omit the optional -ResourceGroupName parameter.
Note
From here, you can connect to the restored database using SQL Server Management Studio to perform needed tasks, like database swapping. See point in time restore.
View LTR policies
This example shows how to list the LTR policies within a server.
# get all LTR policies within a server
$ltrPolicies = Get-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $serverName | `
Get-AzSqlDatabaseLongTermRetentionPolicy
# get the LTR policy of a specific database
$ltrPolicies = Get-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName `
-ResourceGroupName $resourceGroup
Clear an LTR policy
This example shows how to clear an LTR policy from a database.
This example shows how to list the LTR backups within a server.
# get the list of all LTR backups in a specific Azure region
# backups are grouped by the logical database id, within each group they are ordered by the timestamp, the earliest backup first
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location
# get the list of LTR backups from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName
# get the LTR backups for a specific database from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -DatabaseName $dbName
# list LTR backups only from live databases (you have option to choose All/Live/Deleted)
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -DatabaseState Live
# only list the latest LTR backup for each database
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -OnlyLatestPerDatabase
Restore from LTR backups
This example shows how to restore from an LTR backup. Note, this interface did not change but the resource ID parameter now requires the LTR backup resource ID.
# restore a specific LTR backup as an P1 database on the server $serverName of the resource group $resourceGroup
Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId -ServerName $serverName -ResourceGroupName $resourceGroup `
-TargetDatabaseName $dbName -ServiceObjectiveName P1
Important
To restore from an LTR backup after the server or resource group has been deleted, you must have permissions scoped to the server's subscription and that subscription must be active. You must also omit the optional -ResourceGroupName parameter.
If you are using LTR backups to meet compliance or other mission-critical requirements, consider conducting periodic recovery drills to verify that LTR backups can be restored, and that the restore results in an expected database state.
Note
From here, you can connect to the restored database using SQL Server Management Studio to perform needed tasks, such as to extract a bit of data from the restored database to copy into the existing database or to delete the existing database and rename the restored database to the existing database name. See point in time restore.
Delete LTR backups
Delete backups that are retained for a specific database with an LTR policy.
Important
Deleting LTR backup is non-reversible. To delete an LTR backup after the server has been deleted you must have Subscription scope permission. You can set up notifications about each delete in Azure Monitor by filtering for operation 'Deletes a long term retention backup'. The activity log contains information on who and when made the request. See Create activity log alerts for detailed instructions.
In the Azure portal, navigate to the logical server of the Azure SQL Database.
Select Backups. To view the available LTR backups for a specific database, select Manage under the Available LTR backups column. A pane appears with a list of the available LTR backups for the selected database.
In the Available LTR backups pane that appears, review the available backups. Select a backup to delete. Select Delete.
If you use LTR backups to meet compliance or other mission-critical requirements:
Verify the LTR backups are taken as per the configured policy by following steps outlined in view backups section either using Portal, Azure CLI or PowerShell.
Consider conducting periodic recovery drills to verify that restore of LTR backups results in expected database state.
Related content
To learn about service-generated automatic backups, see automatic backups
Azure SQL Database
