DB Sync Utility

[Tencntraze]

Basically, I have my development DB on my machine at home, but my employer has his own DB with different data and such at his site that he uses to play around with updated builds of the app I'm writing for him. Sometimes, I need to modify a table or add/remove a stored procedure, and it's rather tedious to look to see what's changed in my DB vs what he has, and equally tedious to keep track of each individual change that I make so that I can send them to him. Is there some sort of easy way to update his DB from mine? Generating a script for the entire DB isn't really a solution since he would lose his existing data this way.

 

[imported_Dhaval00]

What DB are you using? SQL Server, Oracle?

 

[Tencntraze]

SQL Server 2005

 

[imported_Dhaval00]

This is a super-simple task if you have the liberty/basic knowledge of using Integration Services.

Alternatively, just write a .NET app using the ADO.NET SqlBulkCopy API - you'll have to build some logic to reconcile the differences, but shouldn't be too hard. Logic could like:

1) DataSet.Table[0] - Import PKs from primary table.
2) DataSet.Table[1] - Import PKs from secondary table.
3) DataView - Find all the keys that are not in secondary table - these are all the new rows.
4) DataSet.Table[2] - Use keys from the DataView to bring the new data that needs to be imported.
5) Use SqlBulkCopy.WriteToServer to copy all the rows from step 4.

 

[nakedfrog]

I think he wants the whole omelet, field changes, stored procedures, functions, etc.
I've used AdeptSQL Diff in the past, but it's not free. What you're looking for is a SQL diff program, Google it and find one you like 😛

 

[imported_Dhaval00]

Doesn't matter... he can still do it using the SQL Server SMO API from within .NET (this can get tricky).

However, as I mentioned earlier... using Integration Services all you gotta do is drag and drop the 'Transfer Database Task' on the IDE, set a few properties, and run the package. It is super-easy. IMO, the OP doesn't even need to know all the nitty-gritty details of SSIS. This is assuming he/she is at least running SQL Server Standard.

 

[Tencntraze]

I am running 2005 Developer, so all is well. I haven't had the chance to look into any of this yet, but thanks for the pointers.

 

[JACKDRUID]

replication

if you like to learn it, pm me i'll give you notes from a class I taught.