1

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?

0

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.

| improve this answer | |
  • Unfortunely, it's not working: The term 'Microsoft.SqlServer.Dac.BacPackage::Load' is not recognized as the name of a cmdlet, function, script file, or operable program. I used 140 as <version> – Alex Zhukovskiy Dec 18 '17 at 15:09
  • 1
    @AlexZhukovskiy I've updated answer with info on command that may help but I have not tried it (don't have an Azure SQL server setup). – user507 Dec 19 '17 at 13:24
-1

I cant take credit for this. Credit goes to David Browne.

https://stackoverflow.com/questions/51730309/the-term-microsoft-sqlserver-dac-bacpackageload-is-not-recognized

but this

$loadBac = Microsoft.SqlServer.Dac.BacPackage::Load($bacpacFile)

should be this

$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)

Square brackets.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.