Additional Exporting Methods

PurpleD423

Is there a way for SQL Compare to export a database, snapshot or scripts folder to a single file?

We are trying to use SQL Compare to script out our database to the scripts folder; which would then be kept in source control so we can track changes to individual database objects over time. This would also allow us to modify a database object on our master schema within source and would not require us to bring up a database to do this modification. The one step that we are unclear on is how we are going to take the directory of database objects that SQL Compare generates and put that into a single create script that we can then deploy to other machines on our network.

We are looking into purchasing SQL Compare Pro for our development needs, but are unsure on how to resolve this last step in our development process. The one solution that I have come up with for this scenerio would be that the developer would have to sync their development database every time a change is made and use another application to perform the database scripting process. Can your SQL Compare Pro application handle this action? Does one of your other tools perform this operation from a scripts folder?

Thanks for your time.

Matt

Michelle T

If you have a SQL Compare script database, and you want a single create script, you can do the following:

1) Use SQL Compare to compare the script database against the deployment target (or a snapshot of the deployment target - if the deployment target is 'an empty database' you can snapshot an empty script folder to avoid using any live databases in this process at all).
2) Open the Synchronization Wizard
3) At the Action Plan / Modifications / Warnings stage, press the 'View SQL Script...' button
4) Press the 'Save...' button
5) This will save a 'single create script' based on your script database to disk at a location of your choice.

If you want a cleaner-looking script (at the expense of not using transactions to ensure the whole operation can be rolled back if there are any issues) then you can turn on the 'Do not use transactions in synchronization scripts' option when setting up the comparison.

(If you need to do this in a more automated manner, ask and I'll put together some instructions for doing it through the command line or API.)

PurpleD423

Thanks for your timely response.

I tried what you mentioned and that seems to be a viable solution, but when performing the synchronization between two script folders the Show Script button flashes then disappears. I have tried this when doing a sync between two databases, two snapshots and a couple of other combinations and those all seem to work just fine, the Show Script button appears in the lower left-hand corner of the dialog. I also tried this on another machine here and we had the same issue on that machine, the Show Script button flashes when the screen is initially rendered, but then is hidden.

I would also appreciate it if you could put together some other more automated solutions for performing this action as you mentioned in your previous post. Automating this procedure would be the best possible solution for us and would enable our development team to focus on things other than trying to remember to re-generate a create script every time they make a change to our master schema.

Thanks.

Matt

Michelle T

Using the Command Line:

Assuming you are comparing from a script database (ScriptDBOne) to another script database (ScriptDBTwo) and want a change script to make databases with the schema of ScriptDBTwo into databases with the schema of ScriptDBOne.

1) sqlcompare /scripts1:"C:\ScriptDBTwo\"
/makesnapshot:"C:\Snapshots\temporarySnapshot.snp"
2) sqlcompare /scripts1:"C:\ScriptDBOne\" /snapshot1:"C:\Snapshots\temporarySnapshot.snp" /scriptfile:"C:\SQLScripts\OneToTwoSyncScript.sql"

If you want to automatically overwrite the previous output, you can add the /force switch to the command lines.

The reason that the Show Script button is not available when targetting a script folder database is that we don't generate an actual synchronization script for a script folder database - we generate an internal list of the necessary file changes instead, which is an entirely different process.