Create BACPAC File From SQL Database

Azure, SQL Server

BACPAC is a zip file containing SQL Server database metadata and data from the database. This database backup is mainly used to restore the on-premise database to the Azure SQL database. This article will show various ways to create BACPAC file from SQL database.

Is your database BACPAC ready?

Before starting the large BACPAC file export process, we must consider this very important question. There are various ways to determine if the on-premise SQL server database is compatible with the BACPAC.

The 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 also used for database project versioning. Refer to this article to learn how to extract a DACPAC of an SQL server database. It also covers some obvious things to do before you go for a DACPAC.

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

Let’s Now BACPAC.

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

1. Using SQL Server Management Studio 18 or 19 wizard:

This is the most straightforward way to create BACPAC file from SQL database. Follow the 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 small (less than 100 GB, maybe).
  2. The temp folder has enough free space.
  3. User sessions can be active for the duration of the entire BACPAC operation. Interruption in the user session will terminate the BACPAC operation.

2. 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 the 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 it doesn’t exist already.

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

$SQLPackagePath = "C:\Program Files\Microsoft SQL Server0\DAC\bin"
# Location of the sqlpackage utility.

$envPath = [System.Environment]::GetEnvironmentVariable("Path", "Machine")
if (-not $envPath.Contains($SQLPackagePath))
{
    Write-Host "SQLPackage.exe path is not found. Updating the environment variable."
    $newPath = $envPath + ";" + $SQLPackagePath
    [System.Environment]::SetEnvironmentVariable("Path", $newPath, "Machine")
}

4. Restart PowerShell ISE.

5. Verify if you have correctly set up the Sqlpackage utility. Run the below command in the PowerShell ISE to check that.

sqlpackage -version

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

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

Now, we are ready for the BACPAC operation.

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

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

SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$SourceDatabaseName /tf:$BacpacPath$SourceDatabaseName".bacpac" /df:$BacpacPath$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.

create bacpac file from sql database

Pro tips:
1. Sqlpackage.exe export operation temporarily writes data to the temp folder. This can sometimes 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 uses Windows authentication to connect to the source SQL server. To use SQL Server authentication, you must 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’s 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 an SQL Server Agent job.
4. If you run the script from the agent job, ensure the SQL Server Agent Service Account has adequate access to the source SQL server and target windows folder locations for exporting files.

See more

Download Now
Kunal Rathi

With over a decade of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
I am always interested in new challenges so if you need consulting help, reach me at kunalrathi55@gmail.com.

Shopping Cart
Scroll to Top