Exporting database DDL using sqlpackage.exe has different result vs SSMS create table script

Question

Hi guys,

I'm trying to export all the DDL of my DB into a single .sql file to do some documentation auto-composition tests with AI.

I noticed that the command I use as

&"C:\Program Files\Microsoft SQL Server\170\DAC\bin\sqlpackage.exe" `
/Action:Extract `
/SourceServerName:"LOCALHOST" `
/SourceDatabaseName:"DB" `
/SourceUser:sqlpackage `
/SourcePassword:"psw" `
/SourceTrustServerCertificate:True `
/SourceEncryptConnection:True `
/p:ExtractTarget=file `
/TargetFile:"D:\dump\SQL SERVER\DBsql" `
/DiagnosticsFile:"D:\dump\SQL SERVER\DB.log" `
/DiagnosticsLevel:"Error"

I get a different result than the creation script from SSMS, for example with sqlpackage it always puts

(DATA_COMPRESSION = PAGE)

CREATE TABLE [TO40_NOCORE_VIGILAN].[RWA_HERMIONE] (
    ........
)
WITH (DATA_COMPRESSION = PAGE);

which I don't have in my result from SSMS (I know there are advanced preferences to make them explicit)

But is the difference due to the version of sqlpackage.exe being later than the DB?

The result of the entire DB DDL does not seem to me to correspond perfectly to the creation script from SSMS... I have to schedule it automatically

ALEN

Answer 1

Is the table page-compressed or not? If it is not, that's sort of interesting.

I don't know the exact settings for sqlpackage, but I would expect that, given what it is intended for, that it scripts everything about the objects by default.

On the other hand, the defaults in SSMS are more hit and miss. You can change the settings under Tools->Options-SQL Server Object Explorer->Scripting. Below is what I see, and I think this is the default - I can't recall that I've meddled with these settings.