How to Import Database from BACPAC file.
Scenario:
When we get Database backup from sandbox environment from LCS it will be in Bacpac file and we can’t import it directly on any sandbox environment, as SSMS uses bak file database.
High level resolution steps
We can follow the below steps to import BACPAC File.
Rename the existing Database in SSMS.
Download SQL Package and Extract it
Run command in CMD to import database
Detailed resolution steps
Step 1: Download SQL Package from LCS asset library and extract it
Step 2: Turn off the following services.
Step 3: Rename the Existing DB. Right click on DB and select Rename.
Or you can run the below SQL commands
ALTER DATABASE AxDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE AxDB MODIFY NAME = AxDBORIG090724
GO
ALTER DATABASE AxDBORIG090724 SET MULTI_USER;
GO
Step 4: Run CMD as Administrator.
Step 5: Navigate to the Extracted SQL Package Path.
Step 4: Now run the following command
SqlPackage.exe /a:import /sf:k:\DBBac\BackupDB.bacpac /tsn:devf1234-1 /tdn:AxDB /p:CommandTimeout=200000 /TargetEncryptConnection:False
tsn (target server name) – The name of the SQL Server to import into.
tdn (target database name) – The name of the database to import into. The database should not already exist.
sf (source file) – The path and name of the file to import from.
/p:CommandTimeout = 200000 seconds approximately 55 hours timeout for command
/TargetEncryptConnectionLFalse this is the updated script which will keep us away from certificate errors
Step 5: Database restore will start.
It will take multiple hours based on the Database.
After completion the DB will be available in the SSMS. Turn on all the stopped services and do a synchronization in Visual studio.
