Copy Azure SQL Database

Common issues with import/export Azure SQL Database and alternative solutions.

Matt Mansoomi

22 Jan 2025 • 3 min read

One of the common issues when exporting or importing an Azure SQL Database is that users often get stuck at certain points when they're using Azure Portal.

According to Microsoft's official documentation, delays can occur due to the limited number of compute virtual machines (VMs) per region allocated for processing import and export operations. This limitation can result in delays or even cause the process to get stuck for several days.

Once resources are assigned and a request starts processing, the service automatically cancels the request after two days if it hasn't completed. I recently encountered a similar case where a customer tried to export their database to blob storage with the aim of restoring a BACPAC file from the blob storage.

Microsoft recommends using SQLPackage or other tools like Azure Data Studio for such operations. In this particular case, the customer confirmed that they have a procedure to restore the database from their production environment to their UAT/Pre-Prod environments, which are in different subscriptions. They mentioned that the manual process typically took them a few days. I suggested using T-SQL as explained in the Microsoft Learn article "Copy a Database - Azure SQL Database."

There are different options available depending on the requirements, such as copying the database within the same logical server, an elastic pool, a different logical server, or a different subscription. We followed the steps, and the entire process only took 10-15 minutes! I want to share the process here as it can be useful for many users and database administrators.

Step-by-Step Process

Connect to the Destination Database and Create the Login in the Master Database

    
  CREATE LOGIN restoreuser WITH PASSWORD = 'PutStorngPassword';
   GO
   CREATE USER [restoreuser] FOR LOGIN [restoreuser] WITH DEFAULT_SCHEMA=[dbo];
   GO
   ALTER ROLE dbmanager ADD MEMBER restoreuser;
   GO

Create the User in the Source Database


  CREATE USER [restoreuser] FOR LOGIN [restoreuser] WITH DEFAULT_SCHEMA=[dbo];
   GO
   ALTER ROLE db_owner ADD MEMBER restoreuser;
   GO

Find the SID for the User Created

In the Master database, run the following command and keep the SID somewhere safe:

SELECT [sid] FROM sysusers WHERE [name] = 'restoreuser';

Create the Same Login and User in the Destination Server with the SID: Connect to the destination server and in the Master database, create the same login and user with the SID obtained in the previous step:


     CREATE LOGIN [restoreuser] WITH PASSWORD = 'PutStorngPassword', SID = 0x0106000000000064000000000000000099927D496EE2314493EA2B603B916A18;
   GO
   CREATE USER [restoreuser] FOR LOGIN [restoreuser] WITH DEFAULT_SCHEMA=[dbo];
   GO
   ALTER ROLE dbmanager ADD MEMBER restoreuser;
   GO

Copy the Database

Connect to the destination database with the user account created earlier, then connect to the Master database and run the following command. It is recommended to use square brackets for server and database names:

CREATE DATABASE my_new_database
   AS COPY OF [norwest-server].[db-dailywork01];

In my lab, the database was restored in the destination server within a few minutes. Please note that in some cases, it might take a bit longer for the database to appear in the Azure Portal.

After finishing the test, I deleted the servers and resources.