How to create bacpac file from SQL server? | export bacpac file sql server | How to create a BACPAC file from SQL Server database?

This article guides,

How to create bacpac file from SQL server? How to create a BACPAC file from SQL Server database? or export bacpac file sql server

AZURE SQL Database is pass offering from Microsoft. We can use AZURE SQL Database as service if you want to minimize the server management.

AZURE SQL Database support DTU and V core-based model. We can choose model based on application requirement. If we choose V core-based model, we have another option available of Elastic Pools.

Elastic Pool is group of resources combine in pool and we can add multiple databases in same pool which can shared resources.

Azure SQL Database does not support traditional way of backup and restore. Backups are managed by Microsoft based on your backup retention policy.

If you want to migrate database from SQL Server to AZURE SQL Database create bacpac file from SQL Server and export it to azure SQL Database.

Step by Step Guide: How to create bacpac file from SQL server?

How to create bacpac file from SQL server using SSMS? or export a database to a BACPAC file using SSMS

Right Click on Database –>Task –> Export data-tier Application

Click finish and bacpac file will be available on drive.

Create bacpac file from SQL server using sqlpackage.exe

sqlpackage.exe is utility which help you to take bacpac from SQL Server or Azure SQL Database. You need to download sqlpackage.exe utility before generating bacpac.

Download SqlPackage for Windows.

Extract the file by right clicking on the file in Windows Explorer, and selecting ‘Extract All…’, and select the target directory.

Open a new Terminal window and cd to the location where SqlPackage was extracted:

How to create bacpac file from SQL server using powershell for Single databases on SQL Server?

Post installation you will see SqlPackage.exe on C:\Program Files\Microsoft SQL Server\160\DAC\bin path.

Open PowerShell window and change directory to C:\Program Files\Microsoft SQL Server\160\DAC\bin path to run below script.

CD C:\Program Files\Microsoft SQL Server\160\DAC\bin

.\SqlPackage.exe /Action:Export /tf:C:\Omkar\myscripts\AW.bacpac /SourceConnectionString:"Server=$SourceServerName;Initial Catalog=AdventureWorks2019_new;Persist Security Info=False;User ID=test;Password=P@55w0rd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=true;Connection Timeout=30;"'

How to resolve Cannot export bacpac in SQL Server Error SQL71564?

How to create bacpac file from SQL server using PowerShell for Multiple databases?

If you want to take bacpac file using PowerShell, below script can help to take bacpac of multiple databases.

sqlpackage.exe is application use to generate bacpac using PowerShell. This .exe file available on C:\Program Files (x86) \Microsoft SQL Server\140\DAC\bin (Change SQL Server version as per your)

Create PowerShell file with below script using .PS1 extension. Make sure sqlpackage.exe is present on server from where you are taking bacpac.

Save below script as bacpac.PS1. In this script change variable value for $userName and $password with SQL Server username and password for Azure SQL database.

#Take bacpac using SQL Authentication

$userName = "test"
$password = "P@55w0rd"
$SourceServerName="DESKTOP-SFVAGSQ"


$DBs = @('AdventureWorks2014','AdventureWorks2016','AdventureWorks2019_new')

[string] $filename = $DBs + $filename  
 
foreach($db in $DBs){

$cmd = "& 'C:\Program Files\Microsoft SQL Server\160\DAC\bin\sqlpackage.exe' /action:Export /tf:C:\Omkar\myscripts\$db.bacpac /SourceConnectionString:""Server=$SourceServerName;Initial Catalog=$db;Persist Security Info=False;User ID=test;Password=P@55w0rd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=true;Connection Timeout=30;"""


#$cmd = .\sqlpackage.exe /action:Export /tf:C:\Omkar\myscripts\$db.bacpac /SourceConnectionString:"Server=$SourceServerName;Initial Catalog=$db;Persist Security Info=False;User ID=test;Password=P@55w0rd;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=true;Connection Timeout=30;"
Invoke-Expression $cmd
}

Run below script to take bacpac of multiple databases. This will Export a database to a BACPAC file

Open PowerShell window and go to path where PS1 file exists to export bacpac file sql server.

& ‘.\bacpac.PS1’ SQLUserName password

Once script executed successfully bacpac file will be available on path mentioned in script.