July 14, 2008 at 10:59 am
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.
July 14, 2008 at 11:36 am
July 14, 2008 at 11:54 am
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 2:30 pm
Do any tools exist natively to SQL Server 2005? Or are 3rd party tools the only option?
Thanks again.
July 14, 2008 at 2:41 pm
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.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 3:30 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply