How to Export SQL Server Database to Bacpac File – Step by Step Process


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.

Problem:

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.

Quick Solution: Users can go for a third party utility i.e. SysTools SQL to Azure Migration Tool to quickly migrate local SQL Database to Azure SQL Database.

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

    1. 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.
    2. Now, in the context menu, select Tasks and then Export Data-tier Application
    3. A wizard will open, you need to click on the Next button and then click on the Export Settings in the Introduction page
    4. 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.

    5. 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
    6. 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

    1. Connect to the desired SQL Azure Server. Click on Object Explorer pane. Right-click the Database folder –> select Import Data-tier Application
    2. After that, Import Data-tier wizard will open and an Introduction page will appear on the screen. Click on Next button
    3. In Import Settings, provide the location from where you want to import the bacpac file. And Click on Next.
    4. 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
    5. The Summary page will appear which confirms all the information provided is correct or not.
    6. At last, you need to click on the Finish button to begin the Import process.
    7. 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

    1. First, download and install SQL to Azure Migration Tool and launch it on your system
    2. SQL to Azure Migration Tool

    3. Click on the Open button to select the desired MDF file which you want to migrate
    4. open

    5. 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
    6. scan mode

    7. After the completion of the scanning process, you can preview the database records of MDF and NDF files.
    8. Click on Migrate to start the migration process.
    9. migrate

    10. 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
    11. add server name

    12. Next, fill the Login Credentials for Azure SQL Server and click on the Connect button.
    13. logins

    14. 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
    15. destination db

    16. The software provides two options to export migrated data : With Only Schema and With Schema and Data. Select the option as per your requirement
    17. with schema & data

    18. After that, click on Migrate button. Once the migration process is completed, the software will show a scanning report of the migrated data.
    19. scan report

      Conclusion

      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.

      Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: