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