I need to create an solution that update my local db version based on changes that was made on my server database. First, I need to check the differences that has exists between these databases and so, based on a number of control, and I will be able to know whom scripts i need to execute in my local db. I will create an table to do control of what was executed and not. But I need an 'insight' on how i will store the script that was executed on my server db to be able to execute it again in my local database to make the update, and make both of them stay in the same 'version'. This need be done just by click on a button on my application, in other words, I pretend to automate this task.

P.S.: Sorry for my English :-) this is the first time that I make an question on a English forum.

  • 1
    When you say "changes" on the server database, do you mean schema / structure changes, or data changes? – RoKa May 21 '13 at 13:08
  • RoKa, I refer to schema/structure changes (new tables, 'alter columns' and so on) – Fabricio May 21 '13 at 13:18

Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)

For sql server : SQL Compare.

Useful resource for automating using Powershell or command line.

If you want to code the solution by yourself using Powershell, then look at Deploying Database Changes with PowerShell

Note: Just to mention, there is Data compare to sync data as well.

  • You could also use plain ole' replication to replicate some of the schema changes for you. msdn.microsoft.com/en-us/library/ms147331(v=sql.105).aspx (Just saying!) – RoKa May 21 '13 at 13:46
  • 1
    @RoKa agreed that replication can be used. But in this case- I would refrain from using it -- what if the OP wants to restore the database at some point ? Then you have to go with the pain of removing and recreating replication. Also replication is not just intended for replicating schema changes, it is for schema + Data. – Kin Shah May 21 '13 at 13:52
  • Thanks for the reply, Kin. But the point is that i need to do this by myself. These tools that you mention I had read about, they are very interesting, however, can't use them. Thanks again! – Fabricio May 21 '13 at 13:57
  • @Fabricio then PowerShell is your Friend (if you dont want to spend in 3rd party tools). Some Opensource tools on codeplex opendbiff.codeplex.com and exportsqlce.codeplex.com – Kin Shah May 21 '13 at 14:01
  • 1
    @Fabricio just be sure that you're not "saving money" just to spend more money (since time is money). I've blogged about this: madelinebertrand.com/2012/04/20/… – Aaron Bertrand May 21 '13 at 14:24

If comparing and synchronization of these 2 databases can do the trick, like Kin suggested, you can use an API to "do it yourself"

ApexSQL Diff API can be integrated into your own application, so you can have "a one-click solution". You can compare and synchronize immediately or just create a synchronization script, live databases, db backups, script folders and databases versioned in a source control. The API can also be used to compare and synchronize database data, not just schema/structure

  • No, I don't but I use their tools almost every day – Carol Baker West May 21 '13 at 14:31
  • @CarolBakerWest a large number of your posts appear to be related to this product. If you ARE affiliated with any vendor this is fine, but we ask that users disclose their affiliations and don't limit their contributions to product endorsements. – JNK May 21 '13 at 14:49
  • @JNK I'm fully aware of that. If I get affiliated with any vendor, I'll make it clear. – Carol Baker West May 21 '13 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.