Restore or copy an Azure SQL Database with resource tags
Published Oct 19 2022 01:16 AM 2,590 Views
Microsoft

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"

 

hugo_sql_0-1666042311324.png

 

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

 

hugo_sql_0-1666046900040.png

 

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

 

hugo_sql_0-1666053310594.png

 

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"

 

 

Co-Authors
Version history
Last update:
‎Oct 19 2022 01:08 AM
Updated by: