|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Tuesday, June 30, 2009 9:03 AM
Points: 142,
Visits: 430
|
|
I'm trying to generate a script to move a development database server to production. Both the dev and production versions of the database already exist. Other than required database object changes, I need to leave the production database server (including existing data) untouched. In other words, I can't just "create" a new database.
Is there anyway to find differences between 2 versions of a database? I was thinking of just generating scripts and doing a text compare. However, I'm guessing there's a less painful way of doing it.
Thanks in advance for your help.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 2:41 PM
Points: 228,
Visits: 377
|
|
ApexSQLDiff is a schema comparing tool.
http://www.apexsql.com/sql_tools_diff.asp
Kindest Regards,
AlexSQLForums
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 12:21 PM
Points: 6,281,
Visits: 3,254
|
|
RedGate has one too, called SQL Compare. You can find that from the link on SSC's upper right corner.
Either one will do what you need.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, June 30, 2009 9:03 AM
Points: 142,
Visits: 430
|
|
Do any tools exist natively to SQL Server 2005? Or are 3rd party tools the only option?
Thanks again.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Wednesday, July 01, 2009 12:21 PM
Points: 6,281,
Visits: 3,254
|
|
Well, you could do a join between various system views.
For example, if you want every proc/function/view that's different, you could do:
select s1.[object_id]
from server1.database1.sys.sql_modules s1
left outer join server2.database2.sys.sql_modules s2
on s1.definition = s2.definition
where s2.object_id is null
That would give you every one where there wasn't a perfect match. You could replace spaces, tabs and carriage returns in the join clause, to make sure they are/aren't the same other than whitespace.
That won't get columns, indexes, etc., but you can expand the principle yourself and do those in the same manner.
Or, you can spend less than $400 for a license of ApexSQL Diff, or $4 less ($395 vs $399) and get a license of RedGate SQL Compare. (The Apex product costs $4 more, but includes a data comparison tool, while the RedGate products for structure compare and data compare are separate products with separate license costs.)
If you can put together the queries easily, go for it. Buying the product is for wimps, but it's also generally cheaper than the DBA's salary for the time spent building your own solution. (Personally, I'm a wimp - I buy when it's easier and cheaper.)
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Tuesday, October 28, 2008 5:46 PM
Points: 61,
Visits: 371
|
|
I can't speak to the Apex tool but I have been using SQL Compare for the last 2 years - it has saved me hours and hours of work - very easy to use and well worth the cost of admission!
Good luck,
Harley
|
|
|
|
