Automatic Update Script

ciaranarcher

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!

Eddie D

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

ciaranarcher

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 = (local).BR

Object type             Name                                                                                                                 DB1 DB2
-----------------------------------------------------------------------------------------------------------------------------------------------------
Table                   [dbo].[TestTable]                                                                                                        <<
-----------------------------------------------------------------------------------------------------------------------------------------------------

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

ciaranarcher

Any further thoughts on this, anyone?

Thanks,
Ciaran

David Atkinson

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

ciaranarcher

That did it! Thanks for the tip.

ciaranarcher

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.

David Atkinson

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

ciaranarcher

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

David Atkinson

Can I ask why it is that you've got additional columns in the target databases that don't exist in source control?

ciaranarcher

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

David Atkinson wrote:

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

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.

ciaranarcher

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