如何通过PowerShell命令覆盖Azure SQL Server中的现有数据库

Azure SQL PowerShell模块提供的New-AzSqlDatabaseCopy没有内置覆盖现有数据库的参数，要实现反复刷新覆盖用户开发库的需求，只需要在执行复制前增加目标库存在性校验逻辑，存在则先删除原有库再执行复制即可。同时对原代码的异常场景做了兼容优化，修改后的完整代码如下：

[String]$tenantId = '' 
[String]$accountId= ''
[String]$subscriptionId = ''
[String]$databaseServereInstance = ''
[String]$sourceDatabaseName = ''
[String]$azureRg = ''
[String]$sourceServer = ''

Write-Information -MessageData '*** Obtain token and connect to Azure AD ***' 

#Obtain the Access Token
[Microsoft.Azure.Commands.Profile.Models.PSAccessToken]$accessToken = Get-AzAccessToken -ResourceUrl 'https://graph.windows.net/'
[String]$dbAaccessToken = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
Write-Output $dbAaccessToken
#Connect to AzureAD
Connect-AzureAD -AadAccessToken $accessToken.Token -TenantId $tenantId -AccountId $accountId

#Get-AzureADGroup
[String]$groupname = 'test' 
[Microsoft.Open.AzureAD.Model.Group]$getGroup = Get-AzureADGroup | Where { $PSItem.DisplayName -eq $groupname }
Write-Output $getGroup.DisplayName

if($getGroup -ne $null)
{
  #Get-AzureADGroupMember
  Get-AzureADGroupMember -ObjectId $getGroup.ObjectId | ForEach-Object -Process `
  {
    [String]$userPrincipalName = $PSItem.UserPrincipalName
    [String]$givenName = $PSItem.GivenName
    [String]$getDbAzSqlAaccessToken = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
    [String]$targetDbName = ""
    [String]$accountName = ""

     if([String]::IsNullOrEmpty($PSItem.GivenName))
     {
        $targetDbName = "$($sourceDatabaseName)-DevDB-$($userPrincipalName)"
        $accountName = $userPrincipalName
     }
     else 
     {
        $targetDbName = "$($sourceDatabaseName)-DevDB-$($givenName)"
        $accountName = $givenName
     }

     # 校验目标库是否存在，存在则直接删除实现覆盖逻辑
     $existingDb = Get-AzSqlDatabase -ResourceGroupName $azureRg -ServerName $sourceServer -DatabaseName $targetDbName -ErrorAction SilentlyContinue
     if ($existingDb) {
         Remove-AzSqlDatabase -ResourceGroupName $azureRg -ServerName $sourceServer -DatabaseName $targetDbName -Force
     }

     # 复制源库到目标位置
     New-AzSqlDatabaseCopy -ResourceGroupName $azureRg -ServerName $sourceServer -DatabaseName $sourceDatabaseName `
     -CopyResourceGroupName $azureRg -CopyServerName $sourceServer -CopyDatabaseName $targetDbName 
     
     # 兼容用户已存在的场景，避免重复创建报错
     [String]$query = "
     IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = N'$($accountName)')
     BEGIN
        CREATE USER [$($accountName)] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;    
     END
     ALTER ROLE db_owner ADD MEMBER [$($accountName)]; 
     "
       
     Invoke-Sqlcmd -ServerInstance $databaseServereInstance -Database $targetDbName -AccessToken $getDbAzSqlAaccessToken -query $query
   }
} 
else
{
  Write-Verbose -Message "AzureAD Group could not be found..."
}

内容的提问来源于stack exchange，提问作者Atdhe Kurteshi