Schema Changes
-
Hi,
Is there any built in tool or functions to find the schema changes in 2014 and 2008r2.
I click on the schema change history from standard report, but it trowed out of memory exception.
I read about DDL Triggeres and CDC will be helpful to track the schema changes.
Is there any another way available? What are options to find the schema changes
-
Use source control.
At any time you want, you can compare your database schema with what is in source control.
If you want to find out who made a change to a database object directly, use a DDL trigger.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Another vote for source control. That's the single best approach.
You could look to third party tools. For example, Red Gate Software (my employer) has a tool called SQL Compare [/url]that will let you compare two copies of a database, a database to a backup, a database to a set of scripts, or even a database to source control, or any of the above combined, to find where schema has changed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Could a database audit specification be another solution?
-
burfos (12/22/2014)
Could a database audit specification be another solution?Sure, but audit compared to what? You have to maintain some sort of baseline. That's one of the many resources that having your database in source control will provide.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
They are absolutely correct. Typically a version is kept as a gold standard in a code vault of some kind and then everything else compared with that.
There are free versions of sql compare tools out there that will get you by, but the one by RedGate is the one thing I make all my clients buy. You can script against it to automate it as well.
Barring that though, you would have to script out something in tsql to say compare 2 tables and tell you if any of the elements are different. It's laborious but possible. You could even script out the prod tables and use them as your gold standard to compare the rest to, but now you're just writing your own code vault, which is what Grant said to begin with (use a code vault).
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply