Exercise - Configure long-term retention policies

7 minutes

Your retail organization must comply with data protection regulations in your jurisdiction. You need to configure a long-term retention (LTR) policy in Azure SQL Database to meet these requirements.

Let's use the Azure portal to set up a policy and then check it in PowerShell. You need to set up the following retention policy to meet your regulatory requirements:

Retention period	Length
Weekly	Eight weeks
Monthly	12 months
Yearly	Five years

Use the Azure portal to configure long-term retention

Let's start by configuring the LTR policy by using the portal.

On the Azure portal menu or from the Home page, select All resources, and then select erpserver-NNNN.
Under Data Management, select Backups.
In the list of databases, select sql-erp-db. Select the Retention policies tab, and then select Configure policies.
In the Long-term retention section, enter the value 8 and select Week(s) under Weekly LTR Backups.
Under Monthly LTR Backups, enter the value 12 and select Month(s).
Under Yearly LTR Backups, enter the value 5 and select Year(s).
Select Apply, and then select Yes.

Use PowerShell to configure long-term retention

You can also configure LTR policies by using PowerShell.

In Azure Cloud Shell, run this command to set a variable to the value of your SQL Server instance:
```
$sqlserver=Get-AzSqlServer
```
To view long-term retention policies for the database server, run this command:
```
Get-AzSqlDatabase `
    -ResourceGroupName <rgn>[sandbox resource group name]</rgn> `
    -ServerName $sqlserver.ServerName `
    | Get-AzSqlDatabaseLongTermRetentionPolicy
```
This step outputs the retention policies for all databases on the server:

Note

You might see a message display after the output: Long Term Retention is not supported : Not supported for master. This message doesn't affect completion of this module.

Run this command to view the LTR policy for the sql-erp-db database:

Get-AzSqlDatabaseBackupLongTermRetentionPolicy `
    -ServerName $sqlserver.ServerName `
    -DatabaseName sql-erp-db `
    -ResourceGroupName <rgn>[sandbox resource group name]</rgn>

Now let's configure the rest of the policy to meet the requirements specified earlier. To configure an LTR policy via PowerShell, run this command:

Set-AzSqlDatabaseBackupLongTermRetentionPolicy `
    -ServerName $sqlserver.ServerName `
    -DatabaseName sql-erp-db `
    -ResourceGroupName <rgn>[sandbox resource group name]</rgn> `
    -WeeklyRetention P8W `
    -MonthlyRetention P12M `
    -YearlyRetention P5Y `
    -WeekOfYear 1

To check that you've applied the new policy, run this command again:

Get-AzSqlDatabaseBackupLongTermRetentionPolicy `
    -ServerName $sqlserver.ServerName `
    -DatabaseName sql-erp-db `
    -ResourceGroupName <rgn>[sandbox resource group name]</rgn>

You should see the following policy configured. It enables a weekly retention of eight weeks, a monthly retention of 12 months, and a yearly retention of five years for the first backup of the year.

ResourceGroupName : <rgn>[sandbox resource group name]</rgn>
ServerName        : erpserver-25078
DatabaseName      : sql-erp-db
WeeklyRetention   : P8W
MonthlyRetention  : P12M
YearlyRetention   : P5Y
WeekOfYear        : 1
Location          :

You can also confirm the retention period in the portal. Open the Azure portal menu or from the Home page, select All resources, and then select ERPServer.
Under Data management, select Backups, then select Retention policies. In the list of databases, check the sql-erp-db long-term retention properties.

You've now configured a retention policy and validated that the policy meets your organizational and regulatory requirements.

Continue

Exercise - Configure long-term retention policies

Use the Azure portal to configure long-term retention

Use PowerShell to configure long-term retention

Feedback