Restore Azure SQL Database locally v2

In the first version of this script, the non-transactional nature of SqlPackage often ends giving you an invalid “copy” of the database that fails to restore.

This new and improved version gets around that foible.

The right Backup

From this Stackoverflow post, I know that I need to grab a database with no active users. A copy of the database will be transactionally consistent, and have no active users creating transactions in the middle of the SqlPackage routine.
Let’s use the really handy Azure SQL only copying command:

CREATE DATABASE CopyOfLiveDB AS COPY OF LiveDB

The Stackoverflow post above recommends using the following to determine if the COPY command has completed…..

SELECT * FROM sys.dm_database_copies

…..but this adds a manual step to the automated process I am trying to create.

For now, I will be adding a delay to the script to give enough time for the copy to complete.

Installing PowerShell Core

I am moving from CMD to PowerShell Core for this version of the script.

I started by downloading PowerShell Core from https://github.com/PowerShell/PowerShell.

After re-opening Windows Terminal, I have a new profile for PowerShell Core:

I used the following PS command from Steve Lee to update to the latest version:

iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI -Preview"

The Steps

1. Install the necessary PowerShell modules if absent.
2. Add SQLPackage.exe path to the environment variable (not entirely necessary, as I am cd’ing to the directory in the script).
3. Get authorised to access the Azure SQL server.
4. Start the database copy and sleep.
5. Create a bacpac of the copied database on Azure and export to local.
6. Drop the Azure database copy.
7. Drop the target database on local SQL Server if it already exists (SqlPackage will not overwrite an existing database). I am also closing any existing open connections by issuing a SINGLE_USER command on the database.
8. Import the bacpac into the local SQL Server, creating the database.

The Script

if (!(Get-Module "SqlServer")) {
	write-host "Installing SqlServer module"
    Install-Module SqlServer -Confirm:$False -Force
}

if (!(Get-Module "Az.Accounts")) {
	write-host "Installing Az.Accounts module"
    Install-Module Az.Accounts -MinimumVersion 2.2.0 -Confirm:$False -Force
}

$PathVariables=$env:Path
 
IF (-not $PathVariables.Contains("C:\Program Files\Microsoft SQL Server\150\DAC\bin"))
{
	write-host "SQLPackage.exe path is not found, Update the environment variable"
	$env:Path = $env:Path + ";C:\Program Files\Microsoft SQL Server\150\DAC\bin;" 
}


Import-Module SQLServer
Import-Module Az.Accounts

Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
write-host "Database copy initiated......."
Invoke-Sqlcmd -ServerInstance YourServer.database.windows.net -Database YourDatabase -AccessToken $access_token -query 'CREATE DATABASE CopyOfAzureDatabase AS COPY OF AzureDatabase'
write-host "Database copy in progress - sleeping for 120 seconds......"
Start-Sleep -s 120
write-host "Awake"
cd "C:\Program Files\Microsoft SQL Server\150\DAC\bin"
write-host "Exporting database......."
.\SqlPackage.exe /a:export /scs:"Server=YourServer.database.windows.net;Database=CopyOfAzureDatabase;User ID=AzureSqlUserName;password=YourAzurePassword;" /tf:"C:\temp\CopyOfAzureDatabase.bacpac"
write-host "Deleting database copy on server......."
Invoke-Sqlcmd -ServerInstance YourServer.database.windows.net -Database YourDatabase -AccessToken $access_token -query 'DROP DATABASE CopyOfAzureDatabase'
Invoke-Sqlcmd -ServerInstance 127.0.0.1 -Database master -U "sa" -P "YourLocalPassword" -Query 'ALTER DATABASE LocalAzureDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
Invoke-Sqlcmd -ServerInstance 127.0.0.1 -Database master -U "sa" -P "YourLocalPassword" -Query 'DROP DATABASE LocalAzureDatabase'
write-host "Importing database......."
.\SqlPackage.exe /a:import /tcs:"Server=127.0.0.1;Database=LocalAzureDatabase;User ID=sa;password=YourLocalPassword;" /sf:"C:\temp\CopyOfAzureDatabase.bacpac"

If you get the following error, in the Azure portal, try refreshing the AZ Active Directory admin for the Azure SQL server. Do this by clicking the “Remove admin” and then “Set admin” to the name of the admin user/group. Invoke-Sqlcmd: Login failed for user ‘’.

If you get the following error, then you may need to increase the sleep time. I was started getting this error out of nowhere after months of using it without any hitches. Executing each script line individually, I noticed the copied database was not showing up in the portal at the time of trying the SqlPackage export.

Error exporting database:Unable to determine the database compatibility level.