Track database changs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Track database changs

Author

Topic

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-02-02 : 00:38:47

Hi,
I need to record all database chenges in table/log file.

Like whenever we create new table,add delete or rename existing columns ...record user name ..table and chenges ...

Is there any log available in sql server 2008 for this ..

how can i do this ..

any idea..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-02 : 01:01:22

yes. use DDL Trigger

http://msdn.microsoft.com/en-us/library/ms190989.aspx

KH
[spoiler]Time is always against us[/spoiler]

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-02-02 : 01:13:27

Thanks for help ....

is there any software logs are there to record all these chenges ..
we can use this trigger .. but sql server 2008 logs is preferred as per requirement..

any idea..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-02 : 01:33:24

You can try SQL 2008's new Change Data Capture (CDC) feature to track these changes. see [url]http://msdn.microsoft.com/en-us/library/bb522489.aspx[/url]

Harsh Athalye
http://www.letsgeek.net/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-02 : 01:37:27

I believe CDC is only for DML not DDL. What rohitmathur describe is DDL.

KH
[spoiler]Time is always against us[/spoiler]

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-02-02 : 01:51:50

I need to capture only DDL ...
Like when ever create,alter,delete table sql serve will record this
table name ..alter column name ,user name ..ect.. in a log file..

Is there any feature available in sql server 2008 ... ?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-02 : 04:01:58

As mentioned before; use DDL triggers. Here is a example of how to do it, I'm sure it's quite similar in 2008:

http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-02-02 : 05:36:28

Hi,
This DDL trigger can write in to windows evetn log file..?

I need to write these events in to some windows log file..not in
database table.

any idea..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-02 : 07:01:34

quote:
Originally posted by khtan

I believe CDC is only for DML not DDL. What rohitmathur describe is DDL.

KH
[spoiler]Time is always against us[/spoiler]

You are right khtan. My bad

I got confused with the fact that CDC keeps metadata information along with actual data.

Harsh Athalye
http://www.letsgeek.net/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-02 : 07:47:38

Create ddl-triggers along with the RAISERROR statement to get stuff logged in the application log:

RAISERROR (
  N'This is message %s %d.', -- Message text.
  10, -- Severity,
  1, -- State,
  N'number', -- First argument.
  5) -- Second argument.
  WITH LOG; -- Log is sent to the application log

Run this code by itself and review the result in the query window along with the application log. This exact message will get posted as an informational.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-03 : 06:29:56

How about using SCHEMA CHANGE HISTORY in-built report?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources