I have Sql Server DB, I know I can Create thru "Generate Scripts" backup for SP and Functions then I just replace inside the script the "CREATE" with "ALTER", that will update the target DB with the latest Functions And SP but how to do that for Tables structures and "User Defined Tables" ?
migrated from serverfault.com Sep 23 '13 at 19:33
This question came from our site for professional system and network administrators.
The process of synchronizing the schema between two databases is not a simple one. In general, you will need to either write change scripts manually using
Either way, any error can cause loss of data on a massive scale if you select the wrong column to delete, or make your
I can recommend you take a look at RedGate's SQLCompare, Visual Studio Ultimate or Premium, and SQL Server Data Tools - which is free. All three will take two databases, backups or scripts and generate the DDL needed to move from one to the other.
Another tool, apart from those already mentioned is ApexSQL Diff
As Mitch has already mentioned, writing update scripts that will synchronize two databases manually is quite a risky job. You can maybe try to compare by creating scripts for all objects and then use a file compare tool to find the deltas between the two schemas and change it. But for even a bit larger databases I would not recommend this method since it’s quite easy to miss some change
For some object types you can use query like this that will give you objects that are not equal but note that this doesn't cover everything.
Disclaimer: I'm working for ApexSQL as a Technical Support Engineer