Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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: /sdn: /tf: /p:TableData= > 2>&1'

Argument

Description

source server name

source database name

file name and location for BACPAC file

the tables for which data are output to the target 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].



This post first appeared on SQLSERVER-DBA.com, please read the originial post: here

Share the post

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

×

Subscribe to Sqlserver-dba.com

Get updates delivered right to your inbox!

Thank you for your subscription

×