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