Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Configure long-term backup retention

  1. Home
  3. Configure long-term backup retention

Go back to DP-300 Tutorials

You may configure a database in Azure SQL Database with a long-term backup retention policy (LTR) to save database backups in separate Azure Blob storage containers for up to ten years. You may then use the Azure interface or PowerShell to restore a database from these backups.

Using the Azure portal

The following sections show you how to use the Azure portal to configure the long-term retention, view backups in long-term retention, and restore a backup from long-term retention.

Configure long-term retention policies

You can configure SQL Database to retain automated backups for a period longer than the retention period for your service tier.

  • Firstly, in the Azure portal, select your SQL Server instance and then click Manage Backups. On the Configure policies tab, select the checkbox for the database on which you want to set or modify long-term backup retention policies. If the checkbox next to the database is not selected, the changes for the policy will not apply to that database.
  • Secondly, in the Configure policies pane, select if want to retain weekly, monthly or yearly backups and specify the retention period for each.
  • Lastly, when complete, click Apply.

View backups and restore from a backup

With an LTR policy, you can see which backups are kept for a single database and recover from them.

  • Firstly, in the Azure portal, select your server and then click Manage Backups. On the Available backups tab, select the database for which you want to see available backups.
  • Secondly, in the Available backups pane, review the available backups.
  • Thirdly, select the backup from which you want to restore, and then specify the new database name.
  • After that, click OK to restore your database from the backup in Azure storage to the new database.
  • Then, on the toolbar, click the notification icon to view the status of the restore job.
  • Lastly, when the restore job is completed, open the SQL databases page to view the newly restored database.

Using PowerShell

The following sections show you how to use PowerShell to configure the long-term backup retention, view backups in Azure storage, and restore from a backup in Azure storage.

Azure roles to manage long-term retention

For Get-AzSqlDatabaseLongTermRetentionBackup and Restore-AzSqlDatabase, you will need to have one of the following roles:

  • Firstly, Subscription Owner role or
  • Secondly, SQL Server Contributor role or
  • Lastly, Custom role with the following permissions:
    • Microsoft.Sql/locations/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read
    • Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/read

For Remove-AzSqlDatabaseLongTermRetentionBackup, you will need to have one of the following roles:

  • Firstly, Subscription Owner role or
  • Secondly, Custom role with the following permission:
    • Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/delete

View LTR policies

This example shows how to list the LTR policies within a server

PowerShell

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

Dp-300 practice tests

Clear an LTR policy

This example shows how to clear an LTR policy from a database

PowerShell
Set-AzSqlDatabaseBackupLongTermRetentionPolicy -ServerName $serverName -DatabaseName $dbName `
-ResourceGroupName $resourceGroup -RemovePolicy

View LTR backups

This example shows how to list the LTR backups within a server.

PowerShell

obtain a list of all LTR backups in a particular Azure area

Backups are organized by logical database id and then arranged by timestamp within each group, with the oldest backup being placed 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 only LTR backups from live databases (you may choose between 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

Delete LTR backups

This example shows how to delete an LTR backup from the list of backups.

PowerShell

remove the earliest backup

$ltrBackup = $ltrBackups[0]Remove-AzSqlDatabaseLongTermRetentionBackup -ResourceId $ltrBackup.ResourceId

Configure long-term backup retention DP-300 online course

Reference: Microsoft Documentation

Go back to DP-300 Tutorials

Menu