skip to Main Content

I’m restoring an Azure SQL Database (Serverless) from a deleted database backup using Get-AzSqlDeletedDatabaseBackup and Restore-AzSqlDatabase PowerShell commandlets. The restore works, but the tags and ComputeModel are not restored with the database.

I’ve tried using Set-AzSqlDatabase:

Set-AzSqlDatabase -ResourceGroupName $resourcegroupname -DatabaseName $databasename -ServerName $servername -ComputeModel "Serverless" -AutoPauseDelayInMinutes 45

Update: I tried the following code and the Kind is set prior to using the Set-AzResource cmdlet, but it doesn’t stick

$resource = Get-AzResource -ResourceGroupName $resourcegroupname -ResourceType "Microsoft.Sql/servers/databases" -Name "$servername/$databasename"

Write-Host "Setting ComputeModel to Serverless..."
$resource.Kind = "v12.0,user,vcore,serverless"
$resource  
# resource.Kind is successfully set on the $resource object

Write-Host "Set-AzResource..."
$resource | Set-AzResource -Force

Anyone have any ideas?

Thank you.

Cheers,

Andy

2

Answers


  1. Chosen as BEST ANSWER

    Thanks to @holger and @Delliganesh Sevanesan for the help, I was able implement a solution that restores the most recent deleted database (SQL Database), adds some resource tags, and sets the ComputeModel to serverless.

    Here's the code:

    <# 
    Purpose: Restore the most recently deleted Azure Sql Database
    
    Dependencies:
        Az.Sql PowerShell module
    #>
    
    # Set variables first
    $resourcegroupname = 'myresourcegroup'
    $servername = 'myservername'
    $databasename = 'mydatabasename'
    
    [hashtable]$tags = @{ 
        application = "testing"
    }
    
    try {
        $deleteddatabases = Get-AzSqlDeletedDatabaseBackup -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename
    } catch {
        Write-Error "Error getting database backups [Get-AzSqlDeletedDatabaseBackup]: " + $_.Exception.Message
        exit(1)
    }
    
    # Get most recent backup in case there are multiple copies
    # Assumes index and order in foreach is the same - proven in test
    Write-Host "Database backups:"
    $index = 0
    $MostRecentBackupIndex = 0
    $MostRecentBackupDate = (Get-date).AddDays(-2) # initialize variable with date from two days ago
    foreach ($db in $deleteddatabases) {
        if ($db.CreationDate -ge $MostRecentBackupDate) {
            $MostRecentBackupIndex = $index
            $MostRecentBackupDate = $db.CreationDate
            Write-Host "Most Recent Database: $($db.DatabaseName) : Created: $($db.CreationDate) : DeleteDate: $($db.DeletionDate)"
        }
        $index++
    }
    
    $deleteddatabase = $deleteddatabases[$MostRecentBackupIndex]
    
    Write-Host "----------------------------------------------------------------------------------"
    Write-Host "Restoring: $($deleteddatabase.DatabaseName) from: $($deleteddatabase.CreationDate) backup"
    Write-Host "----------------------------------------------------------------------------------"
    
    Write-Host "Deleted database info ResourceId: "
    Write-Host $deleteddatabase.ResourceId
    
    try {
        Restore-AzSqlDatabase -FromDeletedDatabaseBackup `
            -DeletionDate $deleteddatabase.DeletionDate `
            -ResourceGroupName $resourcegroupname `
            -ServerName $servername `
            -TargetDatabaseName $databasename `
            -ResourceId $deleteddatabase.ResourceID `
            -Edition $deleteddatabase.Edition `
            -Vcore 2 `
            -ComputeGeneration "Gen5" 
    } catch {
        Write-Error "Error restoring database [Restore-AzSqlDatabase]: " + $_.Exception.Message
        exit(1)
    }
    
    # Wait a few minutes to allow restore to complete before applying the tags
    Start-Sleep -Seconds 180
    
    Write-Host "Applying tags to database..."
    try {
        $resource = Get-AzResource -ResourceGroupName $resourcegroupname -ResourceType "Microsoft.Sql/servers/databases" -Name "$servername/$databasename"
        New-AzTag -ResourceId $resource.Id -Tag $tags
    } catch {
        Write-Error "Error adding tags to database [Get-AzResource, New-AzTag]: " + $_.Exception.Message
        exit(1)
    }
    
    Write-Host "Setting ComputeModel to Serverless..."
    try {
        # Important - must include -AutoPauseDelayInMinutes 60, -MinVcore, and MaxVcore parameters (thanks holger)
        Set-AzSqlDatabase -ResourceGroupName $resourcegroupname -DatabaseName $databasename -ServerName $servername -ComputeModel Serverless -AutoPauseDelayInMinutes 60 -MinVcore 1 -MaxVcore 2
    } catch {
        Write-Error "Error setting serverless mode [Set-AzSqlDatabase]: " + $_.Exception.Message
        exit(1)
    }
    
    Write-Host "Database restore complete."
    

  2. The Get-AzSqlDeletedDatabaseBackup and Restore-AzSqlDatabase PowerShell cmdlets are doesn’t contain a property to get the ComputeModel.

    While Restore and Delete Backup database we don’t require the ComputeModel properties. while setting database we need to require the ComputeModel.

    If you want to get the compute model for the Azure SQL Database you can use, Get-AzResource command to fetch the specific information.

    Thanks @joy wang SO Solution we can get the serverless Azure SQL Database.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search