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.
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.
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.
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