|   Trend Micro Cloud One™
Open menu

Check for Sufficient Point in Time Restore (PITR) Backup Retention Period

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Risk level: Medium (should be achieved)

Ensure that Microsoft Azure SQL databases have a sufficient Point in Time Restore (PITR) backup retention period configured for security and compliance purposes. Azure SQL service automatically creates database backups that are kept between 7 and 35 days. The SQL service uses Azure read-access geo-redundant storage (RA-GRS) to ensure that the backups are preserved even if the primary datacenter becomes unavailable. Prior to running this rule by the Cloud Conformity engine, the PITR backup retention period must configured within the rule settings, on the Cloud Conformity account dashboard. The supported values are: 7, 14, 21, 28 and 35 days.

Reliability

Having an optimal backup retention period set for Azure SQL databases will enforce your backup strategy to follow the best practices as specified in the compliance regulations promoted within your organization. Retaining point-in-time SQL database backups for a longer period of time will allow you to handle more efficiently your data restoration process in the event of a failure.

Audit

To determine if your Azure SQL databases have a sufficient PITR backup retention period configured, perform the following actions:

Using Azure Console

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and note the PITR backup retention period configured for the rule.

02 Sign in to Azure Management Console.

03 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

04 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

05 Click on the name of the SQL server that you want to examine.

06 On the navigation panel, under Settings, select Manage backups to access the backup retention policies created for the SQL databases available within the selected server.

07 On the Manage backups page, check the value (i.e. number of days) available in the PiTR BACKUPS column for each SQL database listed. If the Point in Time Restore (PITR) backup retention period set for the verified SQL database(s) is different than the retention period identified at step no. 1, the selected Microsoft Azure SQL database server does not have a sufficient backup retention period configured for its databases.

08 Repeat steps no. 4 – 7 for each SQL database server available in the current subscription.

09 Repeat steps no. 4 – 8 for each subscription created in your Microsoft Azure cloud account.

Using Azure CLI

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and note the PITR backup retention period configured for the rule.

02 Run Get-AzSqlServer PowerShell cmdlet using custom query filters to list the names of all SQL database servers (and their associated resource groups) available in the current Azure subscription:

Get-AzSqlServer | Select-Object ServerName,ResourceGroupName

03 The command output should return the requested SQL database server information:

ServerName             ResourceGroupName
----------             -----------------
cc-webapp-sql-server   cloud-shell-storage-westeurope
cc-project5-db-server  cloud-shell-storage-westeurope

04 Run Get-AzSqlDatabase PowerShell command using the name of the SQL server that you want to examine as identifier parameter and custom query filters to get the name of each SQL database available within the selected server:

Get-AzSqlDatabase -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server | Select-Object DatabaseName

05 The command output should return the list with the requested SQL database names:

DatabaseName
------------
cc-web-sql-database
master

06 Run Get-AzSqlDatabaseBackupShortTermRetentionPolicy PowerShell command using the name of the SQL database that you want to examine as identifier parameter and custom query filters to get the PITR backup retention period configured for the selected database:

Get-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server -DatabaseName cc-web-sql-database | Select-Object RetentionDays

07 The command output should return the database backup retention period in number of days:

RetentionDays
-------------
            7

If the value returned by the Get-AzSqlDatabaseBackupShortTermRetentionPolicy cmdlet output is different than the retention period identified at step no. 1, the selected Microsoft Azure SQL database server does not have a sufficient backup retention period configured for its databases.

08 Repeat steps no. 4 – 7 for each SQL database server created within the current subscription.

09 Repeat steps no. 2 – 8 for each subscription created in your Microsoft Azure cloud account.

Remediation / Resolution

To configure the right Point in Time Restore (PITR) backup retention period for your Microsoft Azure SQL database servers, perform the following actions:

Using Azure Console

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and copy the PITR backup retention period configured for the rule.

02 Sign in to Azure Management Console.

03 Navigate to All resources blade at https://portal.azure.com/#blade/HubsExtension/BrowseAll to access all your Microsoft Azure resources.

04 From the Type filter box, select SQL server to list only the SQL database servers available in your Azure account.

05 Click on the name of the SQL server that you want to reconfigure.

06 On the navigation panel, under Settings, select Manage backups to access the backup retention policies created for the SQL databases available within the selected server.

07 On the Manage backups page, select the SQL database that you want to reconfigure (see Audit section part I to identify the right database), then click on Configure retention button to open the retention policy set for the selected database.

08 On the Configure policies panel, select the value copied at step no. 1 from the Point In Time Restore Configuration dropdown list to set the right PITR backup retention period, in number of days, for the selected Microsoft Azure SQL database. Click Apply to save the configuration changes.

09 Repeat step no. 7 and 8 for other SQL databases available within the selected SQL server.

10 Repeat steps no. 5 – 9 for each SQL server provisioned in the selected Azure subscription.

11 If required, repeat steps no. 4 – 10 for each subscription created in your Microsoft Azure cloud account.

Using Azure Console

01 Sign in to your Cloud Conformity account, access Check for Sufficient Point in Time Restore (PITR) Backup Retention Period conformity rule settings and copy the PITR backup retention period configured for the rule.

02 Run Set-AzSqlDatabaseBackupShortTermRetentionPolicy PowerShell command using the name of the SQL database that you want to reconfigure as identifier parameter (see Audit section part II to identify the right resource), to set the right PITR backup retention period, as copied at step no. 1, for the selected Microsoft Azure SQL database. For example, the following Set-AzSqlDatabaseBackupShortTermRetentionPolicy command request sets the database backup retention period to 35 days:

Set-AzSqlDatabaseBackupShortTermRetentionPolicy -ResourceGroupName cloud-shell-storage-westeurope -ServerName cc-webapp-sql-server -DatabaseName cc-web-sql-database -RetentionDays 35

03 The command output should return the PowerShell cmdlet request metadata:

ResourceGroupName               ServerName            DatabaseName          RetentionDays
-----------------               ----------            ------------          -------------
cloud-shell-storage-westeurope  cc-webapp-sql-server  cc-web-sql-database              35

04 Repeat step no. 2 and 3 for other SQL databases created within the selected SQL server.

05 Repeat steps no. 2 – 4 for each SQL server provisioned in the selected Azure subscription.

06 If required, repeat steps no. 2 – 5 for each subscription created in your Microsoft Azure cloud account.

References

Publication date Oct 26, 2019

Unlock the Remediation Steps

Gain free unlimited access to our full Knowledge Base


Over 600 rules & best practices for and

Get started for FREE

A verification email will be sent to this address
We keep your information private. Learn more.

Thank you!

Please click the link in the confirmation email sent to

You are auditing:

Check for Sufficient Point in Time Restore (PITR) Backup Retention Period

Risk level: Medium