Sql-server – Powershell Restore Azure SQL Database to local with Restore-Database

azure-sql-databaseexportpowershellrestoresql server

I have written a powershell script that uses Start-AzureSqlDatabaseExport & Get-AzureStorageBlob to export my Azure DB to a bacpac and download it locally, the next step is to restore the bacpac to my local SQL DB using Restore-Database, but below fails with "the media family on device is incorrectly formatted".

Restore-Database -dbName "MyDB" -backupFile "C:\MyAzureBackup.bacpac"

Is there a way to get this working with Restore-Database, or is there an alternative command that will do this?

Best Answer

I have not actually tried this (but it peaks my interest to later).

You will not be able to use Restore-Database because it only reads native backup formats from SQL Server, not Azure SQL.

The BACPAC file created from your backup of an Azure SQL database is in a data-tier format. Which in this format contains the schema and data for the database. In order to restore this to a local instance in PowerShell you will have to utilize the DAC objects to do the restore. Which if you are loading the SQLPS module the DAC assembly is loaded for you.

Basic examples that I find online seem to follow this format:

Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin\Microsoft.SqlServer.Dac.dll"
$restoredDatabaseName = 'NewDatabase'
$bacpacFile = "Your BACPAC file you downloaded"
$conn = "Data Source=Server;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;"

$importBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)
$importBac.ImportBacpac($loadBac, $restoredDatabaseName)

#Clean up
$loadBac.Dispose()

Once I get something setup I will test the above myself, just to make sure.

Update

The dbatools PowerShell module now includes commands around managing DacPac files. This can be utilized to perform export and publish operations.

Publish-DbaDacpac. I have not had chance to test this against an Azure SQL instance.

Related Question