When we restore or copy an Azure SQL Database and this database has resource tags, we cannot restore the tags because they are not in the backup. What can we do if we have a policy that requires certain tags to create a new database? in this case, I will provide a solution.
We have an Azure SQL Database with two tags: "environment" and "customer"
AZURE PORTAL:
For now, it's not possible.
AZURE CLI:
https://learn.microsoft.com/en-us/cli/azure/sql/db?view=azure-cli-latest#az-sql-db-restore
hugo [ ~ ]$ az sql db restore --dest-name dbTest_tags_copy --edition Standard --service-objective S3 --name dbTest --resource-group rgHCtest --server srv-hc-test --subscription df371903-cbe0-4634-a402-xxxxxxxxx --time "2022-10-16T11:15:00" --tags environment=test customer=customerOne
if we want to restore the database with the original tags, we will run the following commands:
hugo [ ~ ]$ sameTags=$(az sql db show --name "dbTest" --resource-group "rgHCtest" --server "srv-hc-test" --query tags --output yaml | tr ":" = | tr -d ' ' | tr '\n' ' ')
hugo [ ~ ]$ echo $sameTags
customer=customerOne environment=production
hugo [ ~ ]$ az sql db restore --dest-name dbTest_original_tags_copy --edition Standard --service-objective S3 --name dbTest --resource-group rgHCtest --server srv-hc-test --subscription df371903-cbe0-4634-a402-xxxxxxx --time "2022-10-16T11:15:00" --tags $sameTags
If you want to copy the database to another server, you can run the following Azure CLI command:
Copy a database - Azure SQL Database | Microsoft Learn
az sql db copy --service-objective S3 --name dbTest --resource-group rgHCtest --server srv-hc-test --subscription df371903-cbe0-4634-a402-xxxxxxxxxx --tags environment=test customer=customerOne --dest-resource-group rgHCtest --dest-server srv-hc-uksouth --dest-name dbTest_tags_copy
or copy it with the same tags:
hugo [ ~ ]$ sameTags=$(az sql db show --name "dbTest" --resource-group "rgHCtest" --server "srv-hc-test" --query tags --output yaml | tr ":" = | tr -d ' ' | tr '\n' ' ')
hugo [ ~ ]$ echo $sameTags
customer=customerOne environment=production
hugo [ ~ ]$ az sql db copy --service-objective S3 --name dbTest --resource-group rgHCtest --server srv-hc-test --subscription df371903-cbe0-4634-a402-xxxxxxxxx --tags $sameTags --dest-resource-group rgHCtest --dest-server srv-hc-uksouth --dest-name dbTest_original_tags_copy
You can also use the following PowerShell script to copy the database:
Connect-AzAccount
Select-AzSubscription -SubscriptionId df371903-cbe0-4634-a402-xxxxxxxxxxx
$SourceDatabase = Get-AzSqlDatabase -ResourceGroupName "rgHCtest" -ServerName "srv-hc-test" -DatabaseName "dbTest"
$tags = @{"environment"="Test"; "customer"="customerOne"}
New-AzSqlDatabaseCopy -CopyDatabaseName "dbTest_tags_copy" -CopyResourceGroupName "rgHCtest" -CopyServerName "srv-hc-uksouth" -DatabaseName "dbTest" -ResourceGroupName $SourceDatabase.ResourceGroupName -ServerName $SourceDatabase.ServerName -Tag $tags -ServiceObjectiveName "S3"
If you want to use the original tags:
Connect-AzAccount Select-AzSubscription -SubscriptionId df371903-cbe0-4634-a402-xxxxxxxxxxx $SourceDatabase = Get-AzSqlDatabase -ResourceGroupName "rgHCtest" -ServerName "srv-hc-test" -DatabaseName "dbTest"
$tags = $SourceDatabase.Tags
New-AzSqlDatabaseCopy -CopyDatabaseName "dbTest_original_tags_copy" -CopyResourceGroupName "rgHCtest" -CopyServerName "srv-hc-uksouth" -DatabaseName $SourceDatabase.DatabaseName -ResourceGroupName $SourceDatabase.ResourceGroupName -ServerName $SourceDatabase.ServerName -Tag $tags -ServiceObjectiveName "S3"