Restore an Azure SQL Database from one server to another server

By:   |   Updated: 2018-03-09   |   Comments (12)   |   Related: > Azure Backup and Restore


Problem

There is a requirement from an application support team to restore the latest version of a production Azure SQL database to an equivalent Azure database in the test environment. Is it possible to accomplish this?

Solution

Yes, the restore of an Azure SQL database can be done to another Azure database server, but it is a slightly different process as compared to regular database restores done on-premises. This tip describes the step by step process to quickly complete the restore.

Identify the Azure databases on the portal

First, log on to the Azure portal: https://portal.azure.com/.

If you don’t have an account already, you can setup a trial edition https://azure.microsoft.com/en-us/free/ and get a $200 credit for 30 days.

Once you are on the portal, click on the “SQL Databases” option on the left to view the Azure databases. From the screenshot below, the requirement is to restore the production database called –“mohammedProd” on to the UAT database “mohammedUAT” which is on a designated Azure UAT server. As a first step, let's back up the production database –“mohammedProd”.

Azure SQL Databases on portal

Log on to Azure production server

From the screenshot above, click on the production database name-“mohammedPROD”. This will take you to the screenshot as shown.

Production Azure Server

Once you hover the mouse pointer over the server name (see above picture), the “Click to copy” option will appear which can be used to copy the full production server name. If you are unsure which login to use to connect to this server, click on the server name to get that information. Once you click on the server name, the below screenshot will appear.

Production_Server_Login

You can use the login on the right under “Server Admin” to connect to your Production SQL instance. Using the login, connect to the production SQL Server using the latest version of SQL Server Management Studio (SSMS). If you don’t have SSMS already installed, you can download the latest version from this link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

Once done, the production database on the Azure server will appear as below.

Azure Prod instance

Backup the production SQL Azure database

Once you have connected to the Azure server as shown above, you can export the database to a BACPAC file and use that for restoring on to another server. In order to export the schema and data to a logical BACPAC file, right click on the database name and follow the steps as shown.

Export_Data-tier-application

Once you click on the “Export Data-tier Application” option, you will see this.

Export data-tier application intro

Review and click on “Next”.

Export Settings

Here, you have the option to save the BACPAC locally or to a storage account within Azure. In this example, we will store the BACPAC file locally. Once you select the location, click on “Next” which will take you to the “Summary” section as shown.

Export Data-tier Summary

Review the details shown on the screen and click on “Finish”. This will begin the process of exporting the schema and data from the production database. This process will take some time to complete as it depends on factors like size of the database and current activity being performed on the database. Once the export process completes, you will see this confirmation screenshot with the results.

Export Data-Tier application Results

The export process needs to have completed successfully in each of the steps to generate a successful copy of the BACPAC file.

Restore BACPAC file on to the Azure test server

Now that you have saved the production BACPAC file locally, you can import it on the Azure test server. Connect to the Azure test server similar to how you connected to the Azure production server. Once done, right click on the database node and click on “Import Data-tier Application” as shown below.

Restore on UAT server

Once you click on the “Import Data-tier Application” option, you will get this introduction screen.

Import data-tier application Intro

Review the screen and click on “Next”.  Once you clicked on “Next”, you will get this option to select the BACPAC file to import.

Import_BACPAC

Here, you have the option to browse for the BACPAC file that you exported from production. Once you have selected the exported BACPAC file, click on “Next” which will take you to the Settings option as shown.

Import Data-tier app settings

Based on your requirement, you have the option to select the settings which include the below options:

  • Edition of Microsoft Azure SQL Database
  • Maximum database size (GB)
  • Service Objective

Once the settings are selected, click on the “Next” option which will take you to the “Summary” section as shown.

Import Data-Tier Application- Summary

Review the contents in the “Summary” section and click on “Finish”. Once done, it will start the process of importing the database as shown.

Import Data-tier Importing database

This process will take some time depending on the size of the BACPAC file and the settings you had opted for while importing the BACPAC file. Once the process completes, you will get this status window as shown below.

Import Data-tier application Results

The import process is successful. You can connect to the Azure test server to confirm that the database has been restored from the BACPAC file.

Newly restored Database from prod

You can also confirm from the Azure portal that the database is successfully restored on the Test server.

Azure portal after DB restore

If the requirement was to retain the same database name as was originally present on the test server then you may want to delete the older version of the database on the test server. Once done, rename the newly restored database to that of the old one. This will help to avoid changing any connection strings wherever applicable.

Script out logins from production and transfer to test server

After the restore to the test server, users may still not be able to connect successfully to the database. In order to fix the access issues that may arise on the test server, ensure to grant the required privileges on the test server for the users. You can script out user permissions as shown below and test for access with the users.

Script out users
Next Steps
  • With this tip, you will be able to restore a SQL Azure database from one server to another.
  • Try this tip using your own Azure subscription.
  • Try this tip using a small test Azure database of a 1GB or smaller.
  • To get familiar with Azure, refer to the numerous tips available at this link.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-03-09

Comments For This Article




Thursday, July 4, 2024 - 9:23:24 AM - Steph Back To Top (92360)
The problem with this methodology which is far old by the way it that when you select the AZURE Storage account to basically put the BACPAC directly in AZURE Cloud and expect to use it there to speed up the process for importing the DB in the AZURE Native server, AZURE will still put the BACPAC file locally on your server then when complete it will copy it to AZURE Container (not really smart, but why not) .. The real prob is when you use this same BACPAC File from the container to do the Import Data-Tier , AZURE is stupid enough to actually only retrieve the BACPAC file from the container and put it back locally on your server !!!!! and then the import Data-Tier process is done locally thus the speed you were expecting is never happening as whatever option you select it will always happen locally on your server .... Go figure !! and we call that great technology !!! LOL

Wednesday, December 6, 2023 - 1:44:34 AM - hari Back To Top (91791)
can you guide the azure database override process?

Thursday, February 27, 2020 - 3:29:12 PM - Mohammed Back To Top (84823)

hi Priya,

I had tried this before between SQL Server on Azure VM and a SQL managed instance and it worked.

Thanks

Thursday, February 27, 2020 - 1:58:44 AM - Priya Back To Top (84817)

Is this process valid for a sql database managed instance also?

Wednesday, March 20, 2019 - 6:14:04 PM - Mohammed Back To Top (79354)

Thank you MJS

very useful information

Wednesday, March 20, 2019 - 10:22:46 AM - MJS Back To Top (79347)

Thank you for the detailed backup/restore solution. This solution involves creating a copy of the data and restoring that copy between the source and destination with the BAKPAC serving as a "container" for the data.

As a alternate solution, try this from the Azure Portal.

1) Create a new SQL Server (logical server) with no databases. Place the new SQL Server in the same subscription and resource group as the source database. This new SQL Server will serve as a "container" for transporting a backup copy of the source database.

2) From the source database, select the copy option and copy the database to the new SQL Server.

3) Move the new SQL Server into the same subscription and resource group as the destination database [the database to be overwritten by the restore].

4) From the new SQL Server, copy the database [just created in above step] into the destination SQL Server and destination resource group.

5) Rename or delete the destination database that is being restored from the source database.

6) Rename the database just restored into the destination SQL Server giving it the original name of the database to be refreshed.

7) Delete the database in the new SQL Server or use it again to refresh other lower environments.

Keep the new SQL Server around for future transport of the same or other databases. An empty SQL Server logical server with no databases has no cost in Azure.

I cannot take credit for the above solution. It was provided by a very clever Microsoft employee. Thank you

Thursday, January 24, 2019 - 5:21:03 AM - Mohammed Back To Top (78863)

Abdiel,

Thanks, I haven't tried it out, I will check when I can. Thanks.

Wednesday, January 23, 2019 - 1:53:43 PM - Abdiel Back To Top (78856)

thanks for this helpful post. Can you please share how we can do the same with Azure SQL Data Warhouse, refresh UAT from Prod?

Thanks a lot

Thursday, January 17, 2019 - 1:02:19 PM - Mohammed Back To Top (78804)

Good question Danny, export process will not be consistent if there are transactions happenning.

Please refer this link: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export

"For an export to be transactionally consistent, you must ensure either that no write activity is occurring during the export, or that you are exporting from a transactionally consistent copy of your Azure SQL database."

Thursday, January 17, 2019 - 9:12:34 AM - Danny Hodgson Back To Top (78798)

 Can the DB have transactions happening during the export process?

Wednesday, April 11, 2018 - 3:35:58 PM - Mohammed Back To Top (75677)

Just see if this helps?

https://stackoverflow.com/questions/19990779/unable-to-connect-to-target-server-when-restoring-bacpac-file-to-sql-server-20

Wednesday, April 11, 2018 - 11:43:05 AM - Andres Castrillo Back To Top (75675)

I'm having this error:

 

Could not import package Unable to connect to master or target server You must have a user with the same password in master or target server














get free sql tips
agree to terms