Solved

How To Track Changes in SQL 2005 databases?

Posted on 2011-09-28
6
169 Views
Last Modified: 2012-08-13
Hi all,
how to track the changes happening in database?

I have a database, some process is trying to change the recovery model to simple and set it back to full.

so, how to identify these kind of scenarios?
please help, Thanks!
0
Comment
Question by:msdba
6 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
Comment Utility
I would run a profiler trace to capture what is happening.  Does it always happen at a specific time or is it random?  Make sure that you capture the login name, starttime and the textdata columns.

Greg


0
 

Author Comment

by:msdba
Comment Utility
Thanks for the comments, Is there any other way other than running profiler?
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 250 total points
Comment Utility
When the recovery model is changed, I believe, something is written to the SQL Server log.  There maybe something in the Windows Application log too.  I'm not sure how much information is given though.

Greg

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 125 total points
Comment Utility
You can create a trigger on the server for alter database command

create Trigger ResetRecoveryMode
on all server
for ALTER_DATABASE
as
begin
declare @tsql nvarchar(max)
SELECT @tsql=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
if(CHARINDEX('RECOVERY',@tsql)>0)--verify whether the user is altering the recovery model
print 'some body alter the database RECOVERY setting'
rollback --if find the user alter the recovery model,then rollback
--alter database AdventureWorks
--SET RECOVERY FULL
end


Ref: http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/24ab1c40-310d-4bae-95f3-891ecdf8fa4c/
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
Comment Utility
If this is a large corporation and you had SQL Server 2008 you could use Policy Based Management to prevent this from happening:
http://msdn.microsoft.com/en-us/library/bb510667.aspx:

A better approach is to control who/what has permission to ALTER the Recovery Model.
0
 

Author Closing Comment

by:msdba
Comment Utility
Thanks 2 All for the valuable comments.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now