incremental updating of database
ddorval
Posts: 12
As part of the Deploy process of our build system I'm trying to update a database in an unknown state to the latest build. (a script is also generated)
This was working before comparing the scripts folder from svn for the build with a the physical database being deployed to. I was getting an script to update based on the differences.
But no longer. Since getting migration scripts to work I know have a script that is anything but incremental. The comment block in the script generated says to run on database with Rev 0 to upgrade to Rev. 642....except that the database is already at Rev. 642 and the extended property is set (from a previous deploy). For some reason even though the database exactly matches the scripts folder sqlcompare wants to add the column I've dropped and drop the column I've added all so that it can run the migration script again which moves the column and data from one table to another... EXCEPT GUESS WHAT, the data is GONE.
I get nothing but grief from these tools, everything I think I have it working it doesn't anymore.
I tried specifying /revision2 with the revision number (even though determining that from msbuild will be difficult) but it just gave me an error:
ERROR2012-12-28 07:47:21 – The database ****.****db is not linked to SQL Source
INFO 2012-12-28 07:47:21 – Control
INFO 2012-12-28 07:47:21 – The database ******.*****db is not linked to SQL Source Control
INFO 2012-12-28 07:47:21 – RedGate.Shared.Utils.InvalidStateException
INFO 2012-12-28 07:47:21 – at RedGate.Shared.Utils.Aver.#j43(String #Wt6, Object[] #ead)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#dRzc(Int32 #HXi)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#pcmb(Project #zNSb, DataSourceNumber #o9rc)
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#pcmb(Project #zNSb, DataSourceNumber #o9rc)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#mcmb()
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#wOr()
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#kcmb()
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#kcmb()
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#OUb()
INFO 2012-12-28 07:47:21 – at #i9G.#OsPb.#6sc(String[] #ead)
Well of course it's not linked to source control it's a QA database. I can't seem to find any documentation for a sql source control command line so it's not getting linked.
Is there a way to tell sqlcompare to use it's own extended property to determine the rev # of the target database? I realize I need to specify the revision number for the scripts folder (which I do with /revision1:). But if the Source and Target are both at Rev 642 and already synced by sqlcompare the next time around I want it to know that nothing needs to be done.
Why can't sqlcompare seem to compare 2 things and figure out what's different? Is it because migration scripts just breaks everything? It's bad enough I had to make a bunch of empty migration scripts to deal with a merge and revert and reapplication of changes because sqlcompare didn't seem to care about the final product but each step along the way.
This may be mostly ranting, but I really do need to get this to work...yesterday if possible, but my guess is just like with static/linked tables it's just not going to work.
This was working before comparing the scripts folder from svn for the build with a the physical database being deployed to. I was getting an script to update based on the differences.
But no longer. Since getting migration scripts to work I know have a script that is anything but incremental. The comment block in the script generated says to run on database with Rev 0 to upgrade to Rev. 642....except that the database is already at Rev. 642 and the extended property is set (from a previous deploy). For some reason even though the database exactly matches the scripts folder sqlcompare wants to add the column I've dropped and drop the column I've added all so that it can run the migration script again which moves the column and data from one table to another... EXCEPT GUESS WHAT, the data is GONE.
I get nothing but grief from these tools, everything I think I have it working it doesn't anymore.
I tried specifying /revision2 with the revision number (even though determining that from msbuild will be difficult) but it just gave me an error:
ERROR2012-12-28 07:47:21 – The database ****.****db is not linked to SQL Source
INFO 2012-12-28 07:47:21 – Control
INFO 2012-12-28 07:47:21 – The database ******.*****db is not linked to SQL Source Control
INFO 2012-12-28 07:47:21 – RedGate.Shared.Utils.InvalidStateException
INFO 2012-12-28 07:47:21 – at RedGate.Shared.Utils.Aver.#j43(String #Wt6, Object[] #ead)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#dRzc(Int32 #HXi)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#pcmb(Project #zNSb, DataSourceNumber #o9rc)
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#pcmb(Project #zNSb, DataSourceNumber #o9rc)
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#mcmb()
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#wOr()
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#kcmb()
INFO 2012-12-28 07:47:21 – at #i9G.#wamb.#kcmb()
INFO 2012-12-28 07:47:21 – at RedGate.SQLToolsCommandLine.CommandProcessor.#OUb()
INFO 2012-12-28 07:47:21 – at #i9G.#OsPb.#6sc(String[] #ead)
Well of course it's not linked to source control it's a QA database. I can't seem to find any documentation for a sql source control command line so it's not getting linked.
Is there a way to tell sqlcompare to use it's own extended property to determine the rev # of the target database? I realize I need to specify the revision number for the scripts folder (which I do with /revision1:). But if the Source and Target are both at Rev 642 and already synced by sqlcompare the next time around I want it to know that nothing needs to be done.
Why can't sqlcompare seem to compare 2 things and figure out what's different? Is it because migration scripts just breaks everything? It's bad enough I had to make a bunch of empty migration scripts to deal with a merge and revert and reapplication of changes because sqlcompare didn't seem to care about the final product but each step along the way.
This may be mostly ranting, but I really do need to get this to work...yesterday if possible, but my guess is just like with static/linked tables it's just not going to work.
Comments
Your call reference is F0068426.
Thanks!
1. SQL Source Control does not have a command-line.
2. SQL Compare uses SQL Source Control, if it is installed, to do the retrieval of the revision from your repository. In other words, it re-uses components from the SQL Source Control installation.
3. When a database gets linked to source control, the Red Gate extended properties are created - these tell source control what the current revision of the database is in source control. Particularly the "SQLSourceControl Database Revision" extended property.
4. In order for SQL Compare to find the migration scripts, you can specify their location using the /migrationsFolder argument, specifying a folder on disk.
Hopefully this helps.