Adventures of a wannabe geek!

Ranting within

Using Sql Compare From the Command Line

In March, I wrote a post about how to generate sql schema scripts from a SQL CE4 database file from the command line. After working out how to do this I wanted to be able to take the generate sql server schema (in .sql file) and compare it against an existing schema. The resulting differences would then generate a sql change script.

I tried Sql Compare, a Redgate tool, and this seemed to prove effective for what I was able to do. After doing a bit of research I was able to find out that Sql Compare isn’t able to compare individual .sql files. BUT you could give it a directory path and compare ALL the .sql files in a directory.

Previously I generated a schema.sql file and placed this file in the following folder: c:\temp\schema

I had an old sql schema file that from previously investigatations with .sdf file. I took one of those files and put it in c:\temp\oldschema

I was then able to run the command as follows from the command prompt:

cd “C:\Program Files\SQL Compare 8”

sqlcompare.exe
    /scr1:”C:\temp\new”
    /scr2:”C:\temp\oldschema”
    /o:Default
    /ScriptFile:”C:\temp\SqlChangeScript.sql”

This script effectively says to compare the old schema files (scr2) against the new schema files (scr1) . O:/default specifies that the default options for comparison and synchronisation will be used. The resulting sql change script will be saved as SqlChangeScript.sql. I could have changed the last option from /ScriptFile to /sync which would have run the synchronisation in place and not produced the change file. I wanted the change file for version control and release history.

When running this through the command line I was able to see the following:

image

This worked perfectly and generated a change script that has everything rolled in a transaction to make sure no errors will run. The nice touch here is that there are messages printed when an action is started.

I didnt want to keep 2 schema files on my build server for comparison so I then started to look at how to compare the new schema file against the database itself. Low and behold this is also available with Sql Compare and works as follows:

sqlcompare.exe
    /scr1:”C:\temp\new”
    /Server2:”<server name or ip>” /db2:”<database name>”
    /o:Default
    /ScriptFile:”C:\temp\SchemaSyncScript.sql”

The only difference here is to change the /scr2 to actually be a database instead of another sql server file.Again this worked as expected and created a nice sql change script that I could then use for database deployment.

If you have any aspirations of automated database deployment then I think that you should try the trial of Sql Compare. It has been very useful for me. I’m just very glad my company already had a company of it I could use for the build server. As this process can be run from command line it can also be run from MSBuild and as part of a TeamCity build. To be continued……………