Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe

The SSMS installation comes with a command line utility called sqlpackage.exe . Commonly used to export sql server databases to Azure.

On a typical default installation the path will be like : C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe

One of it’s features is the compatibility checks ,generating a report of potential issues 

The basic structure for using the compatibility checks is :

1. 'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file > /p:TableData=< schema_name.table_name > > < output_file > 2>&1'

An example of the command line. Note : you need to add a table with some data. Tip:pick a small table:

        sqlpackage.exe /Action:Export /ssn:MYSERVER\INSTANCE /sdn:DB1  /tf:c:\temp\DB1.bacpac  /p:TableData=dbo.Address > C:\temp\DB1.txt  2>&1

An example output:

Connecting to database 'DB1' on server 'MYSERVER\INSTANCE'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Validating schema model
*** Error exporting database:Validation of the schema model for data package failed.
Error SQL71564: The element [myid] has been orphaned from its login and cannot be deployed.
Error SQL71501: View: [dbo].[vw_GetDateFields] has an unresolved reference to object [dbo].[syscolumns].
Error SQL71501: View: [dbo].[vw_GetDateFields] has an unresolved reference to object [dbo].[sysobjects].
Error SQL71501: View: [dbo].[vw_GetDateFields] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[syscolumns].[id], [dbo].[syscolumns].[syscolumns] or [dbo].[sysobjects].[syscolumns].