A while ago I had to restore a backup from SQL Azure on my local machine – I got the backup made (a .bacpac file) and started my usual restore procedure.
I usually use the restore UI when restoring a new backup locally but it’s a bit different when restoring an azure backup.
Right click on “Databases” in the object explorer and choose “Import data-tier application” to bring up the wizard.
Find the backup and choose the database and file paths and start.
Just as simple as restoring a regular backup, however – after a while I got the following error message.
Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
in the “importing database” step.
Hmm, that was weird. But I could ofcourse have run out of memory, but I had plenty of memory available and my instance was set to use an unlimited amount of memory.
I tried a few more times and kept getting the same error, after a bit of googling I found something called sqlpackage.
I opened up a command line and found the sqlpackage.exe – which is bundled when installing sql server.
In my case it was located here “C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin”.
Running the following command with a couple of parameters started up the process to import the database, and after a bit it had succeeded
SqlPackage.exe /a:Import /sf:C:\backup.bacpac /tsn:localhost /tdn:databaseName /p:storage=file
/tsn: targetservername
/tdn: target database name
/a action
/sf sourcefile
The parameters are self explanatory, but the documentation is available here https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx
If sqlpackage is not installed, you can get it via the SQL Server Data Tools download.