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

troyhunt

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?

Chris Auckland

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.

troyhunt

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

Chris Auckland

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.

David Atkinson

@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

TetonSig

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

David Atkinson

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