I'm trying to validate the integrity of one of our SQL Server 2016 databases by using sqlpackage.exe to extract the dacpac and validate the schema:
./sqlpackage.exe /DiagnosticsFile:"diagnostics_users.txt`" /Action:Extract /TargetFile: "users.dacpac" /SourceConnectionString:"connstring" /p:VerifyExtraction=true"
When I run it, I get the output
Resolving references in schema model Successfully extracted database and saved it to file
However, when I check the source database, there is a stored procedure which references a table in a database that does not exist. If I try to run it in SSMS, I get the error:
Invalid object name '[Old_Database].dbo.VIEWER_LOG'. However, SqlPackage did not flag this as an error
When I search in the diagnostic logs, I do see the following:
Microsoft.Data.Tools.Diagnostics.Tracer Verbose: 1 : ColumnResolver: #34027998 ResolvedDescriptor for column: ResolvedDescriptor: [DATE_TIME_STAMP] Potentials(1): [SqlColumn : P1 - [OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP]] Available Column Sources: ColumnSource VIEWER_LOG (affinity = 1) with no known columns
There is a table on the target database called Viewer_Log, but, not with the schema OLD_DATABASE.dbo.VIEWER_LOG.DATE_TIME_STAMP, since OLD_DATABASE.DBO does not exist. Shouldn't SqlPackage be flagging this as an error when it resolves references? Is there a configuration available to detect and flag this?