Unable to view LTR Backups for Azure SQL Database servers

Published 03-13-2019 07:26 PM 1,848 Views
Microsoft
First published on MSDN on Nov 13, 2018
Consider that you have configured the LTR Backup for your Azure SQL Database and you are not able to view it on the Portal.

Figure 1: LTR configure policies pane



And when you click on available backups you do not see the list of backups.


Figure 2: Available Backups pane

This might be related to a permissions issue on the subscription level. Basically, the LTR Backups are related to the subscription and not the server, so if the server was deleted those LTR backups would still exist as they are associated with the Subscription.

Now to give yourself permission to view and restore the LTR backups, please make sure that you have one of the following permissions on the subscription level:

  1. Subscription Owner or

  2. SQL Server Contributor role in Subscription scope or

  3. SQL Database Contributor role in Subscription scope


Please note that you can grant permissions for your users using the PowerShell cmdlets, full tutorial can be found here: I cannot see the Long-Term Retention (LTR) backups

If more granular control is required, you can create custom RBAC roles and assign them in the Subscription scope.

For Get-AzureRmSqlDatabaseLongTermRetentionBackup and Restore-AzureRmSqlDatabase the role needs to have the following permissions:

Microsoft.Sql/locations/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionBackups/read Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/read

For Remove-AzureRmSqlDatabaseLongTermRetentionBackup the role needs to have thefollowing permissions:

Microsoft.Sql/locations/longTermRetentionServers/longTermRetentionDatabases/longTermRetentionBackups/delete





Now after you grant the required permissions to view the LTR backups on the Subscription level, you will be able to view them at the Avaiable Backups pane as shown in image 3 below.


Figure 3: Available Backups after granting the required permissions
%3CLINGO-SUB%20id%3D%22lingo-sub-369123%22%20slang%3D%22en-US%22%3EUnable%20to%20view%20LTR%20Backups%20for%20Azure%20SQL%20Database%20servers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369123%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Nov%2013%2C%202018%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Consider%20that%20you%20have%20configured%20the%20LTR%20Backup%20for%20your%20Azure%20SQL%20Database%20and%20you%20are%20not%20able%20to%20view%20it%20on%20the%20Portal.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2F2018%2F11%2Fimage121.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2F2018%2F11%2Fimage122.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89077i7E96870E20EB3CAA%22%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fmsdnshared.blob.core.windows.net%2Fmedia%2F2018%2F11%2Fimage122.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%20Figure%201%3A%20LTR%20configure%20policies%20pane%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20And%20when%20you%20click%20on%20available%20backups%20you%20do%20not%20see%20the%20list%20of%20backups.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89078i25EC2C079CE9EEEA%22%20%2F%3E%20%3CBR%20%2F%3E%20Figure%202%3A%20Available%20Backups%20pane%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20might%20be%20related%20to%20a%20permissions%20issue%20on%20the%20subscription%20level.%20Basically%2C%20the%20LTR%20Backups%20are%20related%20to%20the%20subscription%20and%20not%20the%20server%2C%20so%20if%20the%20server%20was%20deleted%20those%20LTR%20backups%20would%20still%20exist%20as%20they%20are%20associated%20with%20the%20Subscription.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Now%20to%20give%20yourself%20permission%20to%20view%20and%20restore%20the%20LTR%20backups%2C%20please%20make%20sure%20that%20you%20have%20one%20of%20the%20following%20permissions%20on%20the%20subscription%20level%3A%20%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3ESubscription%20Owner%20or%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Server%20Contributor%20role%20in%20Subscription%20scope%20or%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Database%20Contributor%20role%20in%20Subscription%20scope%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%20Please%20note%20that%20you%20can%20grant%20permissions%20for%20your%20users%20using%20the%20PowerShell%20cmdlets%2C%20full%20tutorial%20can%20be%20found%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fazuresqldbsupport%2F2018%2F10%2F23%2Fi-cannot-see-the-long-term-retention-ltr-backups%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20I%20cannot%20see%20the%20Long-Term%20Retention%20(LTR)%20backups%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20If%20more%20granular%20control%20is%20required%2C%20you%20can%20create%20custom%20RBAC%20roles%20and%20assign%20them%20in%20the%20Subscription%20scope.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20For%20%3CSTRONG%3E%20Get-AzureRmSqlDatabaseLongTermRetentionBackup%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20Restore-AzureRmSqlDatabase%20%3C%2FSTRONG%3E%20the%20role%20needs%20to%20have%20the%20following%20permissions%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Microsoft.Sql%2Flocations%2FlongTermRetentionBackups%2Fread%20Microsoft.Sql%2Flocations%2FlongTermRetentionServers%2FlongTermRetentionBackups%2Fread%20Microsoft.Sql%2Flocations%2FlongTermRetentionServers%2FlongTermRetentionDatabases%2FlongTermRetentionBackups%2Fread%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20For%20%3CSTRONG%3E%20Remove-AzureRmSqlDatabaseLongTermRetentionBackup%20%3C%2FSTRONG%3E%20the%20role%20needs%20to%20have%20thefollowing%20permissions%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Microsoft.Sql%2Flocations%2FlongTermRetentionServers%2FlongTermRetentionDatabases%2FlongTermRetentionBackups%2Fdelete%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Now%20after%20you%20grant%20the%20required%20permissions%20to%20view%20the%20LTR%20backups%20on%20the%20Subscription%20level%2C%20you%20will%20be%20able%20to%20view%20them%20at%20the%20Avaiable%20Backups%20pane%20as%20shown%20in%20image%203%20below.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89079iF3A9B2E52E5E1471%22%20%2F%3E%20%3CBR%20%2F%3E%20Figure%203%3A%20Available%20Backups%20after%20granting%20the%20required%20permissions%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369123%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Nov%2013%2C%202018%20Consider%20that%20you%20have%20configured%20the%20LTR%20Backup%20for%20your%20Azure%20SQL%20Database%20and%20you%20are%20not%20able%20to%20view%20it%20on%20the%20Portal.%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Mar 13 2019 07:26 PM
Updated by: