Automatic Update Script

ciaranarcherciaranarcher Posts: 28
edited July 29, 2011 4:01AM in SQL Compare Previous Versions
Hi

We're using SQL source control on over a dozen databases and I am working on a way to get a batch script to update local databases copies overnight using SQL Compare.

I've a batch file that checks out a copy of the database code from SVN into a temp folder, does a compare between that folder and the local database, and then syncs them.
sqlcompare /scripts1:"C:\temp\temp-svn\BR" /database2:BR /options:iu /sync /ignoreparsererrors

This works great, but I need to ensure that any local work is not overwitten, so any new tables, or columns in tables are left as they are in the target database. I tired using the following flag, but it doesn't seem to be working:
/exclude:Missing:Different

Can anyone offer some guidance on the correct flag to be using in this circumstance?

Thanks in advance!

Comments

  • Eddie DEddie D Posts: 1,779 Rose Gold 5
    Thank you for your post into the forum.

    I suspect that you need to set the "exclude" switch.
    The syntax is /exclude:<type>:<name>

    You could comma-separate types, eg /exclude:Missing,Different

    You could also try two exclude switches, eg /exclude:Missing /exclude:Different

    I hope the above answers your question.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Hi Eddie - thanks for you response.

    That's not doing the trick for me I'm afraid.

    So to test this I have created a new table (TestTable) in my target database, and everytime I run the command:
    sqlcompare /scripts1:"C:\temp\temp-svn\BR" /database2:BR /exclude:Additional /options:iu /ignoreparsererrors /sync
    

    I get the following:
    Summary Information
    ===============================================
    DB1 = C:\temp\temp-svn\BR
    DB2 = &#40;local&#41;.BR
    
    Object type             Name                                                                                                                 DB1 DB2
    -----------------------------------------------------------------------------------------------------------------------------------------------------
    Table                   &#91;dbo&#93;.&#91;TestTable&#93;                                                                                                        &lt;&lt;
    -----------------------------------------------------------------------------------------------------------------------------------------------------
    

    This blitzed my new table from the target database.

    I need the sync to spare any additional tables, any edited table, and really just add any new objects and any changed objects.

    FYI:
    /exclude:Missing,Different
    
    ...throws an error, it doesn't like that comma I think.

    Thanks in advance,
    Ciaran
  • Any further thoughts on this, anyone?

    Thanks,
    Ciaran
  • To not remove additional objects on the target you need to use /exclude:additional in conjunction with /exclude:different
    sqlcompare /scripts1:"C:\temp\temp-svn\BR" /database2:BR /options:iu /ignoreparsererrors /exclude:different /exclude:additional /sync
    
    Be aware that this is case sensitive so the 'd' in different and 'a' in additional needs to be lowercase. We are aware that this is incorrect in the documentation and we will be fixing this for the next release.

    Do let us know if you experience any further issues.

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • That did it! Thanks for the tip.
  • Wait - I'm actually sort of halfway there now :)

    The options above will preserve any added columns on my local copy, and preserve any added objects (like tables) in my local copy.

    But if someone adds a column to a table in the scripts folder via SVN that I have not changed, then it doesn't see that change unless I remove the /exclude:different flag.

    But of course if I do that, then any columns I have added to my local database will be removed in the sync.

    Is there any way around this?

    Thanks in advance.
  • Unfortunately /ignore:additional only applies to top level objects, and not columns. However, I can see why you might expect this to ignore any additional entities. I'll check with the developers to see if this behaviour could be changed. I'd agree that the safest option would be to always avoid dropping anything that exists on the target that doesn't exist in the source.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Thanks for that - I'd be very interested to know if it can be changed, or if there is some other day to ensure automatic update of local databases from SVN.

    The solution we have is not all the way there and of questionable benefit if the local developer still has to do a manual update SSC in SSMS to ensure his local copy is up to date despite this script running.

    Thanks for your feedback!
    Ciaran
  • Can I ask why it is that you've got additional columns in the target databases that don't exist in source control?
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David

    Well the target database is the local development database for the user, so these columns might be uncommitted work in progress.

    What I'm trying to achieve here is an automatic update of the local database for a user. We have over a dozen databases, and we need to (ideally) have them all take changes automatically rather than the user having to update them using SQL Source Control in SSMS one at a time as this is time consuming for large databases.

    So I need the update to avoid overwriting local changes, add any new changes and if there are any conflicts to throw an error as that will require manual intervention.

    I hope I have the right tool for the job - if there was a way to call SQL Source Control form the command line (rather than via SSMS) then that might be better - if we could force a sync when there are no conflicts.

    I hope that makes sense :)

    Cheers,
    Ciaran

    Can I ask why it is that you've got additional columns in the target databases that don't exist in source control?
  • Interesting. You're looking for a "auto-get-latest-when-there-are-no-conflicts" option.

    Are there developers in your team who are unaware of the 'Get Latest' tab? We're hoping to add a feature in a future version of SQL Source Control that will put red indicators in the Object Explorer when we detect changes in source control that have yet to be taken.
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David

    Yes the "auto-get-latest-when-there-are-no-conflicts" would be perfect! When can you ship it? :)

    And yes, the developers are aware of that Get Latest tab, but sometimes there are cross-database dependencies that mean getting the latest for more than one database to resolve.

    And there is also the wait time (more than a few minutes) for updates on large database - and we have a few of those, so we'd love to be able to schedule that update overnight so the devs can have a up-to-date- version of all databases in the morning.

    And ideally if there were conflicts it would be marked against the relevant database in SSMS to it's clear they need to be resolved.

    I like the 'red indicators' suggestion - that would be a good start. I guess there are many ways to solve this problem. Another option is an 'auto-update-if-no-conflicts' option in SQL Source Control so there is no need to do any updates at all (provided there are no conflicts!)

    As background: we use SVN for our regular source control and we have a large code base. A typical svn update might take a few minutes. So we just have a scheduled task running on every dev machine that runs a batch job to do that overnight both for convenience and to ensure that the lazy devs always have the latest :) If there is a conflict the console window remains opened with the conflict details.

    I guess were looking for a similar sort of workflow with SQL Source Control.

    It's obviously not something I can do now via SQL Compare command line so we'll wait for that option I guess.



    Thanks, Ciaran
Sign In or Register to comment.