BACPAC large SQL Server Database like a pro

BACPAC SQL Database with SSMS 2018

BACPAC is a zip file containing SQL Server database metadata and data from the database. This is kind of a database backup that is mainly used to restore the on-premise database to Azure SQL.
In this article, we will see various ways to BACPAC large SQL Server database.

Is your database BACPAC ready?

This is a very important question that we need to consider before we start the large BACPAC process. There are various ways to find out if the on-premise SQL server database is compatible with the BACPAC.

Most simple and preferred way is to try its DACPAC.

What is DACPAC?

It is a file that contains a data model (database objects). This is used also for database project versioning. Refer to this article to know how to extract a DACPAC of SQL server database. It also covers some obvious things to do before you go for a DACPAC.

Once you have a successful DACPAC extract of the database, we are now eligible for the data tier export operation (BACPAC).

Let’s Now BACPAC.

There are at least a couple of ways to take the BACPAC of SQL Server database.

Using SQL Server Management Studio 2018:

This is the most simple way to export SQL database to BACPAC. Follow below steps to do it.

  1. Right-click on the database in the object explorer and click Tasks and Export Data-tier Application.
  2. Choose storage location: Local disk or Azure Storage account. If you choose a Local disk, specify a location on the local disk. Make sure you have enough free space.
  3. Click Finish.

This option is recommended if:

  1. your database size is not very large (less than 100 GB maybe).
  2. the temp folder has enough free space.
  3. user session can be active for the duration of the entire BACPAC operation. Interruption in the user session will terminate the BACPAC operation.

Using SQLPackage utility:

This is the most flexible option for database operation. SqlPackage.exe is a command-line utility authored by Microsoft to perform various database operations like Extract, Export, Publish, etc.

Follow below simple steps to install SQLPackage utility

  1. Download Sqlpackage .NET Core for Windows from here.
  2. Extract the zip file and store the content at your preferred location e.g C:\Program Files\Microsoft SQL Server\150\DAC\bin. Create folders DAC\bin if doesn’t exist already.

3. Add this path in the environment variable. Open the PowerShell ISE with administrator and run below command.

$SQLPackagePath="C:\Program Files\Microsoft SQL Server\150\DAC\bin"
#location of the sqlpackage utility.
IF (-not $PathVariables.Contains($SQLPackagePath))
write-host "SQLPackage.exe path is not found, Update the environment variable"
$env:Path = $env:Path + ";"+$SQLPackagePath

4. Verify if you have correctly setup the Sqlpackage utility. Run below command in the PowerShell ISE to check that.

sqlpackage -version

In case of an error with above command, try the above steps again.

If you want to setup Sqlpackage.exe on platforms other than windows, follow the instructions from this page.

Now, we are ready for the BACPAC operation.

Execute below command in the PowerShell ISE to take the BACPAC of your database.

$OldTemp = $env:TMP
$env:TMP = "D:\backpacs\SqlPackageTemp" #Set temporary location of temp folder.
$SourceServerName = "." 
$SourceDatabaseName = "Sales"
$DacpacPath = "D:\backpacs\"

SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$SourceDatabaseName /tf:$DacpacPath\$SourceDatabaseName".bacpac" /df:$DacpacPath\$SourceDatabaseName".log"

$env:TMP = $OldTemp

Completion of the above command will result in a .bacpac file. In case of any error during the operation, please check the .log file for error information.

Pro Tips:

  1. Sqlpackage.exe export operation temporarily writes data to the temp folder. This sometimes can grow to a large size. If your C drive has limited space, we should temporarily change the path of the temp folder to a preferred location with adequate free disk space.
  2. Sqlpackage.exe export has a lot of parameters. The above script has important ones. We can add more as per needs. You can find a full list of parameters here. For example, the Above script is using windows authentication to connect to the source SQL server. If you want to use SQL Server authentication, you need to add /su:<username> /sp:<password> parameters.
  3. Sqlpackage export operation (BACPAC) can run for a very long time depending on the size of your database. Running this from PowerShell ISE is dependent on the user windows session. If the session is terminated, the BACPAC operation will also be terminated and we need to restart it from the start. To avoid this, we can run the script from a scheduler like SQL Server agent job. If you are running the script from the agent job, make sure that the SQL Server Agent Service Account has adequate rights to connect to the source SQL server and target windows folder locations for exporting files.
export sql server database to bacpac file using SQLPackage utility.

Hope this article helps you in the database export operation for Azure SQL migration.

In my future posts, I will describe:
1. How to upload large BACPAC files to Azure blob storage.
2. How to restore large BACPAC files to the Azure SQL database.

Spread the love

Kunal Rathi

Been working on the Microsoft BI stack for close to a decade. Aspiring Data Architect, Cloud enthusiast.

Leave a Reply