For most of the users, managing and storing vital data is a complicated task. But with a reliable and efficient database, the task become easy. When it comes to the database, SQL Server Database is the most commonly used database. But due to the higher scalability & availability users are switching from SQL Server database to Azure SQL database.
It offers a wide range of cloud services and functionalities such as analytics, networking, storage etc. In terms of cost,security the Azure SQL database is better than SQL Server. Moreover, many of the user are adopting cloud platforms as they are feature-rich and secure.
Now, the question arises, how to migrate SQL Server database to Azure SQL database. Here we come up with this query on how to export SQL Server database to Bacpac file.But first, let us understand what problems are faced by the users while transferring data from SQL Server database to Microsoft Azure database.
The user faces various issues while migrating from SQL Server database to Azure SQL database. The major issue comes with SQL Azure as it does not support BACKUP and RESTORE commands, CREATE DATABASE FOR ATTACH. So, the user can not use these two commands to import SQL Server database to Azure.
Export SQL Server Database to Bacpac File – Step-By-Step Solution
To resolve the problem, we came up with simple solution ie by using bacpac files. Bacpac files contain the database schema and the respective data. It is basically a zip file which contains metadata and data from SQL Server Database. Moreover, it is also used for archiving an existing database in an open format.
The user has to follow the 3-step process to transfer SQL Server database to Azure SQL database using the bacpac file.
- Export Bacpac File in SQL Server Management Studio
- Import a Bacpac File to SQL Azure
- Validate SQL Azure Import
Step 1: Exporting Bacpac File in SSMS
- First, you have to connect to the desired SQL Server instance. Then in the Object Explorer pane, right click on the name of the desired database.
- Now, in the context menu, select Tasks and then Export Data-tier Application
- A wizard will open, you need to click on the Next button and then click on the Export Settings in the Introduction page
- In the Export Settings, you have to insert the desired location to save the bacpac file by clicking on Browse button. You have two options to save the bacpac file either save it to the local disk or to an Azure storage account
Note: You can manually select the database objects to be exported. The option is given in the Advance Tab. By default, all the objects are selected.
- After setting the location of the bacpac file and providing the name of the bacpac file, click on the Next button. Now, a Summary page will appear with all the settings. You have to verify and confirm all the settings
- Finally, click on the Finish button to begin the Export process. After the completion of the Export process, click on the Close button
Step 2: Importing Bacpac File to Azure SQL Database
- Connect to the desired SQL Azure Server. Click on Object Explorer pane. Right-click the Database folder –> select Import Data-tier Application
- After that, Import Data-tier wizard will open and an Introduction page will appear on the screen. Click on Next button
- In Import Settings, provide the location from where you want to import the bacpac file. And Click on Next.
- Database Settings option will appear. Here, you are asked to configure new Azure SQL database by entering the name of the new database and the maximum database size and Click Next
- The Summary page will appear which confirms all the information provided is correct or not.
- At last, you need to click on the Finish button to begin the Import process.
- After importing proces, click on Close button to exit the bacpac wizard
Step 3: Validate SQL Azure Import
You can verify and confirm that the new database has been created successfully in the SQL Azure Server by navigating to SSMS and MS Azure Portal and checking for the respective SQL Azure Server.
SQL Sever to Azure Database Migrator – Quick and Reliable Solution
The user can migrate from SQL Server Database to Microsoft Azure SQL Database with the help of trustworthy and instant solution that is SysTools SQL Server to Azure Database Migrator Tool. With this utility, the user can easily export SQL Server Database to Bacpac File. The software has the user-friendly interface which allows users to work with it in a hassle-free way.
Migrate SQL Server Database to Azure Database – Quick Approach
- First, download and install SQL to Azure Migration Tool and launch it on your system
- Click on the Open button to select the desired MDF file which you want to migrate
- In the Scan Options, select the Advanced Scan option and then check the Auto-detect option if you don’t know the SQL Server version
- After the completion of the scanning process, you can preview the database records of MDF and NDF files.
- Click on Migrate to start the migration process.
- Migration Options panel will appear on your screen. Here, you have to add Server Name of Azure SQL Server Database. By default, the Authentication mode is taken as SQL Server Authentication
- Next, fill the Login Credentials for Azure SQL Server and click on the Connect button.
- After that, click on the drop-down button to select the Destination location to save the database. In the Preferences column, select the database objects as per your choice
- The software provides two options to export migrated data : With Only Schema and With Schema and Data. Select the option as per your requirement
- After that, click on Migrate button. Once the migration process is completed, the software will show a scanning report of the migrated data.
The user can easily migrate from SQL Server Database to Azure Database by following the step by step methods explained in the above section. There are number of methods to export SQL Server database to Bacpac file, but we have discussed method using bacpac files method. For instant migration, it is recommended to opt for an automated solution that is SQL Server to Azure Database Migrator.