In this Azure SQL tutorial, we will discuss how to backup Azure SQL database to local SQL Server.
Microsoft Azure provides a backup facility to backup your Azure SQL database. But sometimes you may want to take a backup of your Azure SQL database to your local machine. This article will help you to achieve this. We will discuss multiple ways to backup an Azure SQL database to a local SQL Server Instance.
- Backup Azure database to local SQL Server using SQL Server Import and Export Wizard
- Backup Azure SQL database to local SQL Server using SSIS tools
- Migrate Azure SQL database to local SQL Server using SQL Server Management Studio
- Export Azure SQL database to SQL Server
Backup Azure database to local SQL Server using SQL Server Import and Export Wizard
Using SQL Server management studio is the easiest way to take a backup of a database. Follow the below steps to backup the Azure SQL database to the local machine:
- Open SQL Server management studio and create a new database on the local SQL Server instance that will store the backup data of the Azure SQL database.
- Once you have created the database, right click on the database in the Object Explorer Window, click on Tasks and then select Import Data to open the SQL Server Import and Export Wizard.
- In the SQL Server Import and Export Wizard, select the Data source as .Net Framework Data Provider for SQLServer.
- Now you will need a connection string that the Wizard will use to connect to the Azure SQL database.
- To get the connection string, open the Azure portal, go to your database and click on Show database connection strings as shown below in the image:
- Copy the connection string for ADO.NET. Verify your login credentials in the connection string. You may need to put your password in the connection string. Enter the password in the connection string and paste the whole connection string into the Connection String option in the SQL Server Import and Export Wizard. Click on Next.
- If you have authenticated to the Azure SQL database successfully, you will be asked to select the data that you want to copy. You can choose any option of your choice. You can either write a query to select a specific table or view or you can choose the tables and views among the list displayed by the Import and Export Wizard.
- After selecting the tables and views, you will be asked to save the SSIS package. You can save this package and execute it again in the future. Click Next and then Finish.
- You will see a screen like the following image if you have successfully copied the data:
Disadvantages of this method:
- You have to take the backup manually.
- Only the data can retrieved, all other logical objects cannot be retrieved with tis method.
Thus, you might have learned how you can take the backup of an Azure SQL database using the SQL Server Import and Export Wizard.
Read How to rename a database in Azure SQL
Backup Azure SQL database to local SQL Server using SSIS tools
In the above method, we used the SQL Server Import and Export Wizard to take the backup of an Azure SQL database. One of the steps included saving an SSIS file.
If you save that SSIS file, you do not need to run the SQL Server Import and Export Wizard again. Instead, you can just run the saved SSIS package and the data will be copied again.
In this section, you will see how you can execute the SSIS package again from the command line.
- We are assuming that you have already saved the SSIS package from SQL Server Import and Export Wizard.
- The extension of the SSIS package is .dtsx. We can execute this package with the DTEXEC.EXE command line utility.
- Run the windows command prompt as an administrator.
- Navigate to the folder containing the SSIS package.
- Execute the SSIS package with the DTEXEC.EXE command.
DTEXEC.EXE /F "backupdata.dtsx"- Once this package is executed, you can verify if the data has been copied or not.
In this way, you can take a backup of your Azure SQL database from the command line.
Read How to create table in azure sql database
Migrate Azure SQL database to local SQL Server using SQL Server Management Studio
You might face a scenario when you want to migrate your Azure SQL database to a local or on-premise SQL Server. In such a case, you can create a bacpac file of your Azure SQL database and restore this bacpac file in a SQL Server database.
You will see how you can do this with the help of SQL Server management studio. The GUI of SQL Server management studio is very user-friendly and makes the process easier.
You can follow the below steps to migrate an Azure SQL database to a local SQL Server:
- In SQL Server management studio, connect to the Azure SQL database that you want to migrate.
- Right click on the database and click on Tasks and then select the option Export Data-tier Application. This will open a Wizard for you. Click on Next to skip the introduction page.
- In the Export Settings, you will be asked to enter the location where you want to store the database bacpac file. Select the option Save to local disk and give the path where you want your new database. Click on Next.
- You will be moved to the Summary page. Review everything and if everything is fine, click on Next.
- Once the process is complete, you will see the following screen:
- Verify in the Windows Explorer if the bacpac file is created or not. If it is created, you can recover this file from the local SQL server instance.
- To recover this file from the local SQL Server Instance, open SQL Server management studio.
- Right click on Databases under the SQL Server instance where you want your migrated database. Click on Import Data-tier Application. This will open a Wizard to import the bacpac file.
- Skip the introduction page by clicking on Next.
- Now select the option Import from local disk in the Import Settings and give the path of the bacpac file that you created earlier. Click on Next.
- Sepcify the name of the new database that you are going to create, specify the paths for the data file and the log file of the new database in the Database Settings page. Click on Next after specifying the paths.
- Review the settings in the Summary page and if everything is fine, click on Next.
- You will see the following screen if the process is successful;
- You can see in the Object Explorer that a new database is created. The new database will be the same as the Azure SQL database. You can delete the Azure database after verifying all the things in the new database. Hence, you have migrated your database.
Thus, in this way you can migrate your Azure SQL database to a local or on-premise SQL Server instance.
Read Cannot open server requested by the login. client is not allowed to access the server
Export Azure SQL database to SQL Server
In the above section, we discussed how to migrate an Azure SQL database to a local SQL Server instance. In this section, you will see an alternative method to export the Azure SQL database to a local SQL Server instance.
You can also create a bacpac file from the Azure portal. But you need to create a storage account in Azure. Once you have created a storage account in Azure, you can create the bacpac file that will be stored in a container in the Azure storage account.
- To create a bacpac file, go to your database in the Azure portal.
- In the Overview, click on Export as shown below in the image:
- Enter the name for the new file and select the subscription type. Also select the storage account and the container where you want to store the file. Select the authentication type and provide the login credentials for the Azure SQL server. Click on OK and the bacpac file of the database will be created at the specified location.
Then you will have two ways to restore the database in the local SQL Server instance:
- You can download the bacpac file from the Azure storage account and restore this file from the local SQL Server instance using the Import Data-tier Application option as discussed in the above method.
- You can also direclty restore the bacpac file from the local SQL Server instance by selecting Import from Windows Azure option in the Import Data-Tier Application wizard.
You may like the following database tutorials:
- How to export data from SQL Server to Excel
- Advanced Stored Procedure Examples in SQL Server
- How to create a table in sql server management studio
- SQL Server Substring Function [9 Examples]
- SQL Server Convert Datetime to date
- Backup and restore SQL Server to Azure Blob storage
- Cannot open backup device operating system error 50 azure
- Azure SQL database column encryption
- Azure SQL database configure firewall
- Azure SQL Database Schema
This is how to export a database from the Azure portal and migrate this database to any local SQL Server instance.
- Backup Azure database to local SQL Server using SQL Server Import and Export Wizard
- Backup Azure SQL database to local SQL Server using SSIS tools
- Migrate Azure SQL database to local SQL Server using SQL Server Management Studio
- Export Azure SQL database to SQL Server
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.