Can the command line run against an include white-list?

Compares and synchronizes SQL database content.

Moderators: Chris Auckland, David Atkinson, richardjm, david connell

Can the command line run against an include white-list?

Postby troyhunt » Tue Feb 08, 2011 3:23 am

I'm running the SQL Data Compare command line and having a bit of an issue excluding tables I don't want in the comparison. Here's my original script:

/scr1:[pulled from Subversion]
/server2:[db server]
/db2:[db name]
/Sync
/Include:identical
/ScriptFile:[output name.sql]
/Force
/Verbose

This syncs any static data I have stored in Subversion just fine, but it also syncs - and hence erases - any data in my transactional tables which don't exist in VCS. I could go and add an /Exclude:table:[my table name] for every non-static table but this is a maintenance headache.

Is it possible to just sync a white-list of tables? Ideally I'd like to only sync data which has been explicitly versioned but at a push, I could add the static table names to the command. I just don't want to do the inverse of that and add every dynamic table to the command. I'm not having any luck playing with various combinations of the /Include and /Exclude switches; can this be done?
troyhunt
 
Posts: 15
Joined: Sun Nov 28, 2010 3:36 am
Location: Australia

Postby Chris Auckland » Wed Feb 09, 2011 10:49 am

Thanks for your post.

Using the /include switch should do what you need. Only the tables stated in the /include will be used in the project.

What happened when you tried this?

Another thing you might like to try is using SQL Source Control 2.0. We currently have an early access release of this product, which will allow you to source control static data directly from SSMS.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby troyhunt » Thu Feb 10, 2011 3:42 am

Hi Chris, that's the trick. The problem I was having was assuming the sync would run against the tables in VCS only without any explicit inclusions or exclusions. What was happening was that the default behaviour was every single table was syncing when I used /Include:identical. When I didn't use it, the exe returned an error code when no changes were required which broke my CI build.

As soon as I removed the black-list and added a white-list, the excluded tables no longer synced as per your suggestion. It's a little bit odd - tables will sync unless there's a white-list they don't appear in - but it has answered the question. Thanks!

And yes, I'm using SQL Source Control 2 EA. I've written about this and the above question in part of a larger post here: http://troy.hn/hTh4yc
troyhunt
 
Posts: 15
Joined: Sun Nov 28, 2010 3:36 am
Location: Australia

Postby Chris Auckland » Thu Feb 10, 2011 11:27 am

Thanks for your reply.

It's a little confusing, but SQL Data compare will use the default mapping if no /include /exclude switches are used. The default mapping is to map everything it can, so basically anything that has a matching PK, unique index or unique constraint will automatically be added to the project.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby David Atkinson » Thu Apr 14, 2011 11:38 am

@Troy - can I check with you that this matter is now resolved satisfactorily in the latest version of SQL Data Compare, which will only consider tables that are in your source controlled repo?

David Atkinson
Product Manager
Red Gate Software
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Postby TetonSig » Fri Jan 06, 2012 11:04 pm

I have setup based on Troy's article and I can confirm that it does just sync the data under source control.
TetonSig
 
Posts: 21
Joined: Fri Jan 06, 2012 10:45 pm

Re:

Postby David Atkinson » Sat Jan 07, 2012 10:55 am

TetonSig wrote:I have setup based on Troy's article and I can confirm that it does just sync the data under source control.


Thanks for confirming this. Did you come across any issues whilst setting up your automation? We're keen to make it easier to get up and running.

David Atkinson
Product Manager
Red Gate
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase


Return to SQL Data Compare Previous Versions

Who is online

Users browsing this forum: Google [Bot] and 0 guests