How to Restore Azure SQL Database from One Server to Another?
Instead of a regular MS SQL Server, users try to restore Azure SQL database from one server to another. This method is a little different from the usual ones but worry not. In this article, we are going to the figure out the ways to restore or copy an Azure DB to another server and even a local server.
Both automated and manual solutions are mentioned below and users can select any of them based on the complexity of the method & user requirements. However, to begin, we must go through one of the user queries to understand the sincerity of the process.
User Query to Restore Azure SQL Database to Another Server
User queries can differ from case to case with minor changes. However, more or less the agenda is the same. Thus, let’s have a look at one of the user queries to begin with.
As we can see here, the user was trying to find a way of resolving this problem just like MS SQL Server. But, clearly it’s not possible and this is why we are here. However, users are not keep in mind that Azure also uses the MDF file so they can still work on the database file for the restore process.
Method #1: How to Restore Azure SQL Database to Another Azure SQL Server?
This process is indeed possible but slightly different from the usual on-premise restore operations. Here, users need to execute the operation in multiple stages. Therefore, users must follow the steps mentioned carefully without any mistakes to avoid critical errors.
Step-1. Firstly, users need to Identify the Azure Databases present on the platform.
- Log in to the Azure portal to begin > Click SQL Databases.
- Decide the source and destination databases here.
Step-2. Log In to the Source Server to continue.
- Hover the Cursor on the Server Name & then Copy it.
- Launch the SSMS and Open the Same Database here.
That’s it. Here we end the usual steps. Now, we are moving toward stage 1 to restore Azure SQL database from one server to another without any hassles.
Also Read: How to open MDF File to Azure Cloud Platform?
Stage – 1 Backup Azure Database Locally
Step-1. Right Click Database > Go to Tasks > Click Export Data-Tier Application to export a BACPAC file.
Step-2. In the Export Data-Tier Application, There will be a new window. Hit Next here.
Step-3. Store the BACPAC File either locally or within Azure. Set Location & Hit the Next button.
Step-4. Review the Details & Hit the Finish button to restore Azure SQL database to another server.
Step-5. To proceed to stage 2, users need to ensure that the export process is successful.
Stage – 2 Restore BACPAC File on the Azure Server
Step-1. Log In to the Destination database, Right-Click DB > Tasks > Import Data-Tier Application here.
Step-2. Simply Click Next on the Introduction screen.
Step-3. Brose the BACPAC File exported in Stage 1 & Hit the Next button.
Step-4. Adjust Following Settings & Click on the Next button.
- Edition of Microsoft Azure SQL Database
- Maximum database size (GB)
- Service Objective
Step-5. Go to the Summary section, review the details & Hit the Finish button.
Step-6. Finally, Go to the Azure Server to check if the process is successful or not.
Method #2: Restore Azure Database to Local Server
The second method that we have is dedicated to copy Azure SQL database to local server.
Here, some steps are similar to the above method and some are similar to the on-premise method. Users need to carefully address the steps in order to not end up creating another mess for themselves.
Let’s begin with the first phase of this method.
Phase-1 Create Copy of the Database with BACPAC
This phase 1 to restore Azure database to local server is an exact copy of what we did in the earlier method. Here, users just need to create a BACPAC file using the same steps as we did earlier.
It involves connecting to the database instance, using the export data-tier application wizard and so on. Therefore, it’s not necessary to explain the entire process again. What’s crucial here is phase 2 which is different.
Phase-2 Import Database to On-Premises SQL Server
Step-1. Open SSMS, Right Click Database > Tasks > Import Data-Tier Application here.
Step-2. Click Next > Select the BACPAC File from the system.
Step-3. Hit the Finish button to copy Azure SQL database to local.
Method #3: Automated Tool to restore Azure database to Local Server or Another Azure
Now, in case users are thinking that manual procedures are quite complex, they’re right. Thus, here we are opting for the MVPs recommended SysTools SQL Recovery Tool in use. This advanced utility works irrespective of the platform. This means, it works on the MDF file and users can use it for both Azure SQL and MS SQL On-Premise servers.
The best thing here is that with the MDF file, it’s just 4- steps to fix the problem. In addition, there are so many advanced features like dual scan mode, multiple export modes, etc. This way, it’s unbeatable when it comes to learning how to restore Azure SQL database to another Azure SQL Server or local one.
Download the tool and then simply follow the steps below:
Step-1. Launch the Tool and then Hit the Open button.
Step-2. Select the MDF File and Set the Scan mode.
Step-3. After scanning, Select Destination & Data.
Step-4. In the end, Hit the Export button to finish.
After that, users can export the MDF file either directly to the server or using the import-export process to get the desired results.
Conclusion
Finally, we know how to restore Azure SQL database from one server to another Azure SQL. Also, there are methods mentioned above that can copy the database to a local on-premise SQL Server. As per experts and Microsoft MVPs, the best solution is the automated tool. This is because it eliminates the complex procedures and directly fixes the MDF file that users can import & use anywhere or copy Azure SQL database to local server as well.