the best approach to copying a dev database to Prod in a different subscription

Question

I have created a database in Development which now for the first time can be copied into production.

After this it would be good to use a more DevOps approach but for now I just want the initial production database

I created a Bacpac from Dev and Imported it into Prod but there are lots of issues with this

1. The database doesnt appear within SQL databases in Azure, You can only see it when you go to the subscription and then resources
2. It has exactly the same Server Admin details and users as Dev but I cant log into it at all with Power BI using any of them
3. For example I can log in, in SSMS with the admin details fine. but not with Power BI (We couldn't authenticate with the credentials)

I have tried a few times but cant get any further with this set up

I then tried to do it via Powershell using the following code

Connect-AzAccount

The SubscriptionId in which to create these objects

$SubscriptionId = 'd017f57e-a193-42c3-a816-bf772d0602db'

Set the resource group name and location for your source server

$sourceResourceGroupName = "dev-uks-project-rg-$(Get-Random)"
$sourceResourceGroupLocation = "UKSouth"

Set the resource group name and location for your target server

$targetResourceGroupname = "prd-uks-project-rg-$(Get-Random)"
$targetResourceGroupLocation = "UKSouth"

Set an admin login and password for your server

$adminSqlLogin = "project_admin"
$password = "REDACTED"

The logical server names have to be unique in the system

$sourceServerName = "dev-uks-project-$(Get-Random)"
$targetServerName = "prd-uks-project-$(Get-Random)"

The sample database name

$sourceDatabaseName = "dev-uks-project-sqldb "
$targetDatabaseName = "prd-uks-project-sqldb "

The ip address range that you want to allow to access your servers

$sourceStartIp = "0.0.0.0"
$sourceEndIp = "0.0.0.0"
$targetStartIp = "0.0.0.0"
$targetEndIp = "0.0.0.0"

Set subscription

Set-AzContext -SubscriptionId $subscriptionId

Create two new resource groups

$sourceResourceGroup = New-AzResourceGroup -Name $sourceResourceGroupName -Location $sourceResourceGroupLocation
$targetResourceGroup = New-AzResourceGroup -Name $targetResourceGroupname -Location $targetResourceGroupLocation

Create a server with a system wide unique server name

$sourceResourceGroup = New-AzSqlServer -ResourceGroupName $sourceResourceGroupName -ServerName $sourceServerName
-Location $sourceResourceGroupLocation -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)) $targetResourceGroup = New-AzSqlServer -ResourceGroupName $targetResourceGroupname
-ServerName $targetServerName -Location $targetResourceGroupLocation
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Create a server firewall rule that allows access from the specified IP range

$sourceServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $sourceResourceGroupName -ServerName $sourceServerName
-FirewallRuleName "AllowedIPs" -StartIpAddress $sourcestartip -EndIpAddress $sourceEndIp
$targetServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $targetResourceGroupname -ServerName $targetServerName
-FirewallRuleName "AllowedIPs" -StartIpAddress $targetStartIp -EndIpAddress $targetEndIp

Create a blank database in the source-server with an S0 performance level

$sourceDatabase = New-AzSqlDatabase -ResourceGroupName $sourceResourceGroupName -ServerName $sourceServerName
-DatabaseName $sourceDatabaseName -RequestedServiceObjectiveName "S0"

Copy source database to the target server

$databaseCopy = New-AzSqlDatabaseCopy -ResourceGroupName $sourceResourceGroupName -ServerName $sourceServerName
-DatabaseName $sourceDatabaseName -CopyResourceGroupName $targetResourceGroupname
-CopyServerName $targetServerName `
-CopyDatabaseName $targetDatabaseName

Clean up deployment

Remove-AzResourceGroup -ResourceGroupName $sourceResourceGroupName

Remove-AzResourceGroup -ResourceGroupName $targetResourceGroupname

However this created two new servers for both dev and Prod and two new resource groups for both dev and prod

I already have the resource group and the Server set up.

So the question is, whats the best process for me to copy the SQL Dev Database over to the Prod server, and I can actually log into it with Power BI using the same user details as I have in Dev?