Data Tier Export to BacPac Fail

1
votes

I need to export an entire SQL Server database - schema, tables, data etc and trying to use the following method.

Login to SSMS with admin credentials, locate relevant database, right click on database, select Tasks >> Export Data-tier Application. Then select a location on the server to save the .bacpac file with the aim of exporting to another server on another network. The process starts okay but then fails and the report for the error has hundreds of errors stating,

"Error SQL71562: Error validating element [dbo].[capt_blockperiod]: Trigger: [dbo].[capt_blockperiod] has an unresolved reference to object [master].[dbo].[sysprocesses].[hostname]. External references are not supported when creating a package from this platform"

How do I resolve these without going through each one please? Is there an alternative way of exporting the entire database that will avoid these issues?

1
sql-serverbacpac

1 Answers

0
votes

Try to generate bacpac in the console

cd C:\Program Files (x86)\Microsoft SQL Server\<SQL_VERSION>\DAC\bin

sqlpackage.exe /a:Export /ssn:myServer /sdn:myDatabase /tf:C:\<MY_PATH>\myBacpac.bacpac

If the logged in user is executing the script, Integrated Windows Authentication will be used. So no need to specify the username or password.

You're sure to bacpac and not dacpac?

For dacpac :

cd C:\Program Files (x86)\Microsoft SQL Server\<SQL_VERSION>\DAC\bin
sqlpackage /Action:Extract /SourceDatabaseName:"MyDatabase" /SourceServerName:localhost /TargetFile:"C:\SomeDirectory\MyDatabase.dacpac"