Restore an Azure SQL Database from one server to another server


By:   |   Updated: 2018-03-09   |   Comments (8)   |   Related: More > Azure

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.


Last Updated: 2018-03-09


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

Thank you MJS

very useful information


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

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

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

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

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

 Can the DB have transactions happening during the export process?


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

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

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools