Generate script in same order in both the database.

Question

Post reply

Generate script in same order in both the database.

Ashwin M N

Hall of Fame

Points: 3668
More actions
November 24, 2008 at 12:31 am

#383443

I want to compare two database by generating the script.

Using generate script I generate script for two database to compare in source safe. But when I open the script I can view that the script generated are in different order.This was not the case in 2000 but in 2005 I always get this type of problem how can I generate the script so that the generated script objects will be in the same order in both the file?Is there any other way to compare the script of two databases?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

I use RedGate’s SQL Compare to check for differences between two DBs. It isn’t free, but it does also include a nice synchronisation function for the price.

Regards,

John Marsh

PS: Alternatively you might be able to use XSQL’s free “Lite” Bundle to do the same job.

www.sql.lu
SQL Server Luxembourg User Group

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Yeah, I was going to recommend SQL Compare from Red Gate too. There's also Apex SQL, they have a nice compare utility. Visual Studio Team System Database Edition comes with a compare utility as well. It's not as good as the other two.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jerry Hung SSChampion Points: 12908 More actions · Answer 3

I recommend SQL Compare from Red Gate too

but there are some free good ones as well

http://www.xsqlsoftware.com/Product/Sql_Schema_Compare.aspx

What is included? The free lite edition of the xSQL Bundle includes the complete suite of xSQL Software products:

*

xSQL Object for schema comparison and synchronization

*

xSQL Data Compare for data comparison and synchronization

*

xSQL Object Command Line utility for unattended schema comparisons and synchronizations

*

xSQL Data Compare Command Line utility for unattended data comparison and synchronization

*

xSQL SDK for integrating comparison and synchronization functionality in your application

SQLServerNewbie MCITP: Database Administrator SQL Server 2005

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 4

I did it in a different way. First I generated the script for sql2000 then in

Sql server 2000 I created a database executed the generated script.

From 2000 I once again generated the script and saved the script in source safe.The same thing I did for second database and compared both the database. By this way I was able to compare two database.

I do not know why the 2005 does not generate the script in order like 2000?

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 5

Just to give a different option: I did an "in-house" approach. I needed to compare our Dev SQL server to our production SQL Server, document any changes, and possibly reapply changes in Dev after restoring a database from production. I did this by writing a web service that accesses SMO (SQL Management Objects), and then using SQL CLR to access the web service. This gave me two scalar functions for getting the creation and drop script of an object. Not as clean and neat as a third party solution, but I like being able to tweak the process.