x

Synching Data Between Databases

We have a data set that is updated for each release of our software projects. We need to modify data between releases of software. We are at a point where we need to make changes to data for a future release, but have not finished a current release, and we do not want to affect the data for the current release. We are looking at creating a copy of the database to add the new data. Our problem is we need to continue to add data to our previous database, and bring this data forward. We also may be making Schema changes to the new database that are not part of the previous database.

This seems like an issue that others would have solved, but I am not able to find a solution. We have investigated SQL Replication, but that looks like our Schema needs to stay the same across databases so that won't work for us.

Thanks,

Tom Harens

more ▼

asked Feb 22, 2010 at 03:54 PM in Default

Tom Harens gravatar image

Tom Harens
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

For the data comparison part, you can consider Red Gate SQL Data Compare or Apex SQL Data Diff, I haven't finished mine yet!

For the schema comparison part, you can consider Atlantis Schema Inspector (my tool), Red Gate SQL Compare or Apex SQL Diff.

Each of those tools will allow you to do filtering in different ways, but you should be able to come up with enough to meet your requirements...

more ▼

answered Feb 22, 2010 at 04:29 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

This would be a manual process, is there a way that we can automatically move data forward to the next version of the database. When data is added to a database we can have as many as 1K records added in mutiple tables, which I think would be hard to mange manualy.

Also we do not need to compare schema. We will be putting schema changes into subsequent databases and they do not need to be put into the previous versions.
Feb 23, 2010 at 12:39 PM Tom Harens
How would using those data comparison tools be a manual process? Set them up, use the command line interface, scheduled task. Job done...
Feb 23, 2010 at 01:01 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

You are also use the TABLEDIFF application MS ship with 2005 and above. Its free.

TABLEDIFF

more ▼

answered Feb 22, 2010 at 04:51 PM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

I was wondering if Transactional Replication would work. I know there can be problems with adding schema to the subscriber, but I believe that would only exist if the data I am trying to merge is being merged into the table I applied the schema change.
Feb 23, 2010 at 12:41 PM Tom Harens
(comments are locked)
10|1200 characters needed characters left

RedGate works well with Data Compare and SQL Compare. They have very intuitive UIs that will help you with your comparison.

If you would rather use free intrinsic tools - TABLEDIFF, as noted, is delivered with SQL Server 2005 - and it is command-line based.

I have used each of these tools and they all do exactly as intended.

more ▼

answered Feb 22, 2010 at 06:51 PM

CFL_DBA gravatar image

CFL_DBA
41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x306
x46

asked: Feb 22, 2010 at 03:54 PM

Seen: 1175 times

Last Updated: Feb 22, 2010 at 03:54 PM