Using migration scripts from command line on unlinked DB

RobVK

We're using SQL Compare 9 with SQL Source Control at the moment to deploy some of our databases using NAnt. The way it currently works is everyone commits using SQL Source Control, and on the build server NAnt checks out the scripts and uses SQL Compare from the command line to compare the scripts folder to the target DB.

We're looking at using migration scripts, but I'm struggling to find resources on how to point SQL Compare 10 at the migration scripts in SVN or on disk.

The resources I've found seem to indicate the target DB has to be linked to SVN. Our live DBs cannot see our SVN server due to network restrictions (live network cannot contact internal network).

Is it possible to either point SQL Compare 10 through the command line at SVN for the code and migration scripts, or point it at the migration scripts if we continue to use the checked out scripts folder as the comparison source?

Thanks.

David Pond

Hi Robert, I've sent you an e-mail with a possible solution.

We will be supporting this feature in SQL Compare 10.1 which will be released in the next couple of weeks.

David

dmcaldwell

Would you mind posting the possible solution on the forum so its available for all of us?

David Pond

I sent RobVK an early build of SQL Compare with a description of some new commandline switches. These switches are now in the current release of SQL Compare (see below). We're working on an easy to use solution, but you can get started with these commands.

This is the landing page for information on CI for databases. http://www.red-gate.com/products/sql-de ... tegration/

RobVK needed to checkout his migrations folder and use the /migrationfolder switch. The /scriptsfolderxml switch is also required because SQL Compare will need to make additional checkouts from the version control system.

/abortonwarnings: none | medium | high
Sets the warning level at which SQL Compare command line will abort.

/assertidentical
Returns exit code zero in the event both database schemas are identical. This can be used at the end of the CI process to confirm that the local copy of the production DB has been successfully updated.

/migrationfolder
This is a path to a directory containing the migration scripts to be used.

/migrationfolderxml [deprecated]
/migrationfolder should be used instead of this option. By allowing the CI system to check out the migrations folder it can display information about recent commits.

/revision1, /revision2
When used with /scripts, it allows SQL Compare to identify the correct migrations scripts. This is necessary because a script folder does not contain information on which revision it is.
When used with /sourcecontrol it also specifies which revision will be checked out by SQL Compare.

/scriptfolderxml
This is a path to a text file that contains an xml fragment which describes a connection to a VCS. This fragment can be copied from the extended properties of a live database connected to SQL Source Control.

/versionusername1, /versionpassword1
These are you username and password to connect to the VCS system. This is required when using /sourcecontrol or when migration scripts are being used.

Sample from a Nant script:

<target name="generate_upgrade_script_schema">
      <exec program="${sqlcompare.executable}" resultproperty="returncode" failonerror="false">
            <arg value="/scripts1:"${latest.scriptsfolder}""/>
            <arg value="/revision1:${latest.revision}"/>
            <arg value="/sourcecontrol2"/>
            <arg value="/revision2:${production.revision}"/>
            <arg value="/scriptsfolderxml:${repo.location.xml}"/>
            <arg value="/migrationfolder:${migration.folder}"/>
            <arg value="/AbortOnWarnings:high"/>
      </exec>
</target>