explicit revision number may destroy target database

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

explicit revision number may destroy target database

Postby maturmel » Fri Sep 07, 2012 4:34 pm

Hi,

I have noticed that when using sqlcompare command-line with parameters:
/sourcecontrol1 and /revision with a specific revision number, if that number does not happen to be a changeset that includes changes to the scripts folder in Source control, SQL compare seems to generate an empty DB as the source of comparison. This results in generating a script that drops everything on the database.

I am looking at this scenario for automation from a build server (TeamCity in this case). I wish to pass explicity the revision number (there's a team city variable suited for that) rather than using HEAD as it seems conceptually more appropriate (in case something is checked in between the moment the build started and when it gets to actually run SQLcompare.exe, as I would have several other build steps before that).

I have been able to replicate the same issue with the GUI SQL Compare, I just put an explicit revision number that is a code check-in (not database objects), and I get everything marked as 'drop' in target db.

For example:
My latest changeset in numbered 6639. My latest changeset of DB objects was 6634. If I use /revision: with a number between 6635-6639, it yield what I described above.

My source control is Vault Standard.
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby Brian Donahue » Mon Sep 10, 2012 5:15 pm

Thanks for contacting us. I'm trying to reproduce the problem using SVN, and not having any success. If I can't get it to happen in SVN, I'll have to set up a Vault server and this can take some time.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby maturmel » Mon Sep 10, 2012 5:31 pm

Thanks for replying Brian. I also got notification that a support ticket was opened - under reference # F0064326.
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby Chris Auckland » Thu Sep 13, 2012 1:48 pm

I've had a chance to try this out with Vault, but I can't reproduce the behaviour either.

This is how I tested it, maybe you can spot what I'm doing wrong:

1) Created a database called 'Test'
2) Linked to Vault repository
3) Created table 'Table_a'
4) Checked in, this was revision 596
5) Deployed revision 596 to another database (Test_B)
6) Outside of SQL Source Control I added a file text.txt to the repository. This was revision 597
7) Set revision 597 as the source and compared to Test_B. This was reported as identical (is there you would expect a drop of Table_a to be scripted?)
8) Added a view 'View_a' - this was revision 598
9) If I deployed 598, it added the view to Test_B
10) Set revision 597 as the source. This time it wanted to drop the view, but that's only because it was created in a later revision, so it worked as I expected.

Is this different to the behaviour you're seeing.

Could you send any reproduction steps that I could try out?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby maturmel » Thu Sep 13, 2012 2:07 pm

Hi Chris,

I believe the following sequence would mimic what I have been doing:

1) create a new table "table_b"
2) check in
3) check in some other file (no SQL related) to the repo
4) try to deploy with source being the revision number obtained by check-in at step #3.

What I'm trying to achieve is to actually deploy code+DB kind of "all at once" as check-ins in steps #2 and #3 in the above would represent my DB changes and VS code related to db changes.

Also, maybe it's something in my command-line parameters (I first found it from CMD line but also replicated in SQL Compare UI). Here's the command line I executed:

SQLCompare.exe /sourcecontrol1 /revision1:<revisionnumber> /scriptsfolderxml:scriptsfolderxml.xml /migrationfolderxml:migrationsfolderxml.xml /db2:<target_db>
/server2:(local) /Include:Identical /Report:BuildPackage\\SchemaDiffReport.html /ReportType:Interactive /ScriptFile:BuildPackage\\SchemaUpdate_version.sql /Force /Verbose

If that can help I am willing to setup a web link to demo it directly on my server.
Marc-Andre
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm

Postby Chris Auckland » Wed Sep 19, 2012 12:09 pm

Sorry for the delay, I missed you last update.

I'm pretty sure your sequence of steps are exactly what I was doing, so maybe this would be good to see happening on your machine.

When would be a good time to schedule a remote session?
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby maturmel » Wed Sep 19, 2012 2:41 pm

Hi Chris,

No worries for the delay. I have been busy as well. :)

For an online session, it works best for me on mornings. Tomorrow and Friday are good for me. Anytime between 9-12 EDT.

Can you email me at marct[AT]technicost.com and we can schedule a phone appointment. I will setup a session using http://join.me

I have a batch file invoking the SQL Compare command line already setup to replicate the issue at will.

Thank you very much.
Marc-Andre
maturmel
 
Posts: 24
Joined: Tue Jan 25, 2011 9:16 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests