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

03 October,2018 by Jack Vamvas

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'

Argument

Description

< server_name >

source server name

< database_name >

source database name

< target_file >

file name and location for BACPAC file

< schema_name.table_name >

the tables for which data are output to the target file

< output_file >

the file name and location for the output file with errors, if any

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].

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer