January 31, 2012 at 10:10 am
Is there a log where I can track all changes to tables ?
January 31, 2012 at 10:16 am
Do you mean data changes in a table, or table definition changes (new columns, dropped indexes, that kind of thing)?
- 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
January 31, 2012 at 10:19 am
table definition changes (new columns, dropped indexes, that kind of thing) is have to show this type of info to auditors
January 31, 2012 at 10:26 am
Some will be in the default trace for a while, but it's not retained forever. Probably best for you to set up some DDL triggers to audit exactly what the auditors want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2012 at 10:29 am
Probably not, unless you've set up something to track that.
The usual ways to track that kind of thing are DDL triggers, traces, and source control.
To check for a DDL trigger (if it's possible someone created one other than you), look in Programmability, Database Triggers, under the database, in Management Studio.
To check for a trace (again, if it's possible someone created one), run: select * from fn_trace_getinfo(0). Trace ID 1 will probably be the default trace. Any other traces will usually have higher IDs than that. You'll then need to use fn_trace_gettable (MSDN.com has details on how) on any traces the first one finds for you, to see what they have in them. The default trace will have some information, like index modifications, but won't have other data, like column definitions. Other traces might have those.
Source control would depend on what product, if any, you are using for that.
If all of those fail, you might (possibly) have the data in the transaction log. You'll need to buy a log parser for that. Search (Bing/Google/whatever) for "sql server log parser" and you'll find a few products for that. I haven't used one in years, so can't recommend one over another. This option should be a sort of last resort, since I think they all cost substantial amounts of money, and they'll only be useful if the database is in Full recovery model and you have the log and log backup files available.
Very last resort is restore from older backups, and compare the table in those vs the current table, and you'll be able to sort of reverse engineer the changes.
Failing those options, no, there is no built-in, default audit trail of object changes in SQL Server.
- 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
January 31, 2012 at 10:30 am
OK I will see if I can find out what thats all about. I mainly run a AS400 add I know how that works but this is all new.
Thanks....
February 1, 2012 at 6:47 am
Are you trying to find changes that were already made, or are you planning for the future and working out options to track changes that might happen?
- 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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply