current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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" ?

share|improve this question

migrated from serverfault.com Sep 23 '13 at 19:33

This question came from our site for system and network administrators.

2 Answers 2

up vote 1 down vote accepted

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 ALTER TABLE commands or use specifically designed software which will generate the ALTER statements for you.

Either way, any error can cause loss of data on a massive scale if you select the wrong column to delete, or make your varchar(30) into a varchar(3) column.

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.

share|improve this answer
    
Thanks alot ...exactly what I have looking for. I have used SQL Server Data Tools. –  the_farmer Sep 24 '13 at 10:26
up vote 2 down vote

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.

select O.name, sm1.definition
from DB1.sys.sql_modules AS sm1
inner join DB2.sys.sql_modules as sm2 on sm1.object_id = sm2.object_id
inner join DB1.sys.all_objects O on sm1.object_id = O.object_id
where sm2.definition <> sm1.definition

Disclaimer: I'm working for ApexSQL as a Technical Support Engineer

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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