When there is no yearly backup retention policy, Get-AzSqlDatabaseLongTermRetentionPolicy returns 0 for WeekOfYear, but Azure Portal displays 'Week 1' as default and Set-AzSqlDatabaseLongTermRetentionPolicy wants a number between 1 and 52.

[sicil1ano]

Description

When there is no yearly backup retention policy, Get-AzSqlDatabaseLongTermRetentionPolicy returns 0 for WeekOfYear.
The value "0" does not make sense, as week 0 does not exist in a solar year.
Furthermore, Azure Portal displays "Week 1" as default and Set-AzSqlDatabaseLongTermRetentionPolicy wants a number between 1 and 52. This means Get-AzSqlDatabaseLongTermRetentionPolicy returns a wrong output, or at least an output that is in contradiction with Azure Portal and Set-AzSqlDatabaseLongTermRetentionPolicy.

Issue script & Debug output

$sourceServerName = "sql-source"
$sourceResourceGroup = "rg-dbsource"

foreach ($sourceDb in $sourceDatabases) {
    if ($sourceDb.DatabaseName -eq "master") {
        Write-Output "Skipping master DB."
        continue
    }
        

    # Get the LTR policy for the current database
    $ltrPolicy = Get-AzSqlDatabaseLongTermRetentionPolicy -DatabaseName $sourceDb.DatabaseName `
                                                          -ServerName $sourceServerName `
                                                          -ResourceGroupName $sourceResourceGroup

    # Display the current LTR policy
    Write-Output "Database: $($sourceDb.DatabaseName)"
    Write-Output "Weekly Retention: $($ltrPolicy.WeeklyRetention)"
    Write-Output "Monthly Retention: $($ltrPolicy.MonthlyRetention)"
    Write-Output "Yearly Retention: $($ltrPolicy.YearlyRetention)"
    Write-Output "Week Year Retention: $($ltrPolicy.WeekOfYear)"
}

Skipping master DB.
Database: mydb
Weekly Retention: P5W
Monthly Retention: PT0S
Yearly Retention: PT0S
Week Year Retention: 0

Environment data

Name                           Value
----                           -----
PSVersion                      7.4.6
PSEdition                      Core
GitCommitId                    7.4.6
OS                             Microsoft Windows 10.0.22631
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Module versions

ModuleType Version    Name                                ExportedCommands                                                                                                                                            
---------- -------    ----                                ----------------                                                                                                                                            
Script     4.0.2      Az.Accounts                         {Add-AzEnvironment, Clear-AzConfig, Clear-AzContext, Clear-AzDefault...}                                                                                    
Script     6.0.1      Az.Sql                              {Add-AzSqlDatabaseToFailoverGroup, Add-AzSqlElasticJobStep, Add-AzSqlElasticJobTarget, Add-AzSqlInstanceKeyVaultKey...}

Error output