CODEWITHSUNDEEP
azureazure-sql-databaserestoreazure-sql-server
djangojazz
djangojazz

Reputation: 13242

Restore to Azure SQL PAAS database a bak file?

Okay so I figured since Azure has a lot of tools and I am failing at best processes to just ask on SO.

What I can do:

BACPAC creation

Creation is directly from SSMS with hovering over the database in Object Explorer. Right Click>Tasks>Export Data Tier Application. Choose a disk location. The caveat is generally to not have a database that is in use querying. So have a copy or system that can be not using the database.

BACPAC restore to Azure

  1. SqlPackage.exe https://learn.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

    When I do it it is similar to: (must have Visual Studio or equivalent Sql management object DLLs installed) "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" /a:Import /sf:(bacpac Location) /tdn:(dbName)/tsn:bvtest.database.windows.net /tu:(user) /tp:(password)

  2. In Azure>SQL Servers>(Top ribbon)>Import database>(have blob storage with a bacpac created to it already). This method appears slightly faster if they are both on same region.

My question is that I have seen articles like this one: https://learn.microsoft.com/en-us/sql/azure-data-studio/tutorial-backup-restore-sql-server?view=sql-server-2017 as well as knowing SSMS pretty well. It seems all options to restore to Azure SQL PAAS are turned off for a 'bak' backup and you have to make a bacpac for going instance to Azure PAAS. The problem is that this takes hours just to make the bacpac on a relatively medium sized database of 120 gigs. Then it takes hours to restore. Other than what I have tried above is there a faster way?

Upvotes: 0

Views: 2237

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15668

You can use Azure Data Migration Service (ADMS) instead of importing/exporting bacpacs. Azure Data Migration Service allows you to migrate data from SQL Server to Azure SQL Database with no downtime if your database does not have "heaps". If your database has heaps you will have to perform an offline migration. Copying/migrating data from a SQL Server ARM (IaaS) Premium Storage (200 GB / 790 tables) to an Azure SQL Database (Premium) takes 1 hour 40 minutes with an offline migration. That is the fastest data migration I have seen as a workaround to import/export bacpacs since SQL Server native backups are not supported on Azure SQL Database (DTU-model / Vcore model).

ADMS performs a validation of your data at the end, if you want.

Upvotes: 1

Related Questions