MSSQL Schema Export Only

Question

Post reply

MSSQL Schema Export Only

myukas

SSC Eights!

Points: 860
More actions
November 27, 2017 at 8:09 am

#329997

Is there an easy way to do schema only exports in mssql

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

myukas - Monday, November 27, 2017 8:09 AM
Is there an easy way to do schema only exports in mssql

You could extract a DAC.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

myukas SSC Eights! Points: 860 More actions · Answer 2

I know about that and it works well however I want to be able to automate this and run a schema extract like once a week

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

myukas - Monday, November 27, 2017 8:19 AM
I know about that and it works well however I want to be able to automate this and run a schema extract like once a week

I don't see a problem with that. Use the SqlPackage command line to extract the DACPAC.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

myukas SSC Eights! Points: 860 More actions · Answer 4

myukas

SSC Eights!

Points: 860

November 27, 2017 at 8:59 am

#1969545

not familiar with that how does it work

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

myukas - Monday, November 27, 2017 8:59 AM
not familiar with that how does it work

Presumably you know how to automate running of an executable?
If yes, everything else you need to know is explained in the link. Just run it, with appropriate parameters, and the DACPAC will be created.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

myukas SSC Eights! Points: 860 More actions · Answer 6

that works thanks much but what is best way to review bacpac file to see what is in it

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

myukas - Monday, November 27, 2017 10:19 AM
that works thanks much but what is best way to review bacpac file to see what is in it

DACPACs and BACPACs are different (BACPACs contain data). I think you mean DACPAC.
1) Try unzipping a DACPAC and viewing the files it contains, or
2) Try deploying a DACPAC to a dev SQL instance to generate an empty database with the desired structure.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

myukas SSC Eights! Points: 860 More actions · Answer 8

i did that and it works just thought there would be a cleaner way to see it like in the gui
guess if i want to automate this it will have to work

Sue_H SSC Guru Points: 90860 More actions · Answer 9

myukas - Monday, November 27, 2017 10:31 AM
i did that and it works just thought there would be a cleaner way to see it like in the gui
guess if i want to automate this it will have to work

Using the DACPAC isn't a bad way to do it.
Not sure exactly of what type of scripts you were looking for but you could also use Powershell to use SMO and the scripting options. There are quite a few examples of doing this if you wanted to pursue that route.

Sue

Phil Parkin SSC Guru Points: 246965 More actions · Answer 10

myukas - Monday, November 27, 2017 10:31 AM
i did that and it works just thought there would be a cleaner way to see it like in the gui
guess if i want to automate this it will have to work

Depends on the whole purpose of this exercise.
Are you coming at it from the wrong angle, perhaps?
We have all of our DB code captured in source control and we have a process set up to compare what's in source control with what is deployed. If any differences are found, an alert is generated.
So, at any time, we know exactly what is deployed and to which environment by looking at the relevant branch in our VCS.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

myukas SSC Eights! Points: 860 More actions · Answer 11

I am a consultant and the client does not have that. This will work for what I need. Thank you for all your help

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

If you want a list of tables as in the SSMS Object Explorer, then you need to apply (import) the DACPAC to a database. The SSMS OE is just reading metadata from the database. There isn't a good tool to do this from a file and display the results. If you unpack the DACPAC, you can see the .SQL file, but it's not necessarily as organized as one might like.

goher2000 SSCertifiable Points: 5479 More actions · Answer 13

right click database --> task -- Generate Scripts
choose objects --> script entier database
set scripting options --> advanced

select 'schema only' for 'types of data to script'

Sue_H SSC Guru Points: 90860 More actions · Answer 14

goher2000 - Tuesday, November 28, 2017 2:27 PM
right click database --> task -- Generate Scripts
choose objects --> script entier database
set scripting options --> advanced
select 'schema only' for 'types of data to script'

And the poster said they wanted to be able to automate it. Don't think that one can be automated which is why there are the other suggestions.

Sue