Solved

SQL Server logs - how to find out what happened and when

Posted on 2011-09-05
7
275 Views
Last Modified: 2012-08-14
Hi,

One of our production databases has been altered over the weekend. In particular a table containing user data has been updated across the board, altering usernames. This should not have happened and we are trying to find out when it happened and who did it.

Our regular DBA is not here and although I do SQL Server DB development I'm not sure what can be done in this situation. It seems like the database and transaction logs have only been retained for the past two days so the oldest available backup includes the unwanted changes committed.

Is there any way to find out when these updates occurred and under what credentials this happened?

Thanks.
0
Comment
Question by:irb56
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 36483312
Do you mean error logs ?

Please see this link: http://msdn.microsoft.com/en-us/library/ms187885.aspx
0
 

Author Comment

by:irb56
ID: 36483350
Thanks for the link but these are not errors we're trying to identify, they are committed update transactions on a particular table. I've done a bit of searching, which so far has lead me to download a trial of a log reader named ApexSQL Log. I believe this is supposed to allow you to audit database changes, including seeing details of the transaction(s) that updated the table in question and the associated login info, etc.

However, I am unsure what the limitations of such a tool will be. How far back can you go in the auditing?
0
 
LVL 15

Accepted Solution

by:
gplana earned 500 total points
ID: 36483422
It depends of your logs. You can go until last checkpoint, which is a point where logs are synchronized with data. For example, in Oracle there is a mode called "archive logs" which permits to have all logs archieved, so you can go virtually to any passed time.

I don't think SQL-server has this functionality, but you can use DTS. See this: article: technet.microsoft.com/en-us/library/bb687457.aspx
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:irb56
ID: 36483426
It looks like the ApexSQL Log tool is excellent. As well as identifying the details of every change, it provides a facility for automatically generating scripts to reverse the change.

I assume there are other SQL Server log reader tools out there. Does anyone have any recommendations, particularly if there are any useful products that don't cost as much as ApexSQL?

Also I am curious regarding the limits of a log reader tool. How is it affected by the database recovery model and the frequency and types of backup? For example, if a database has the full recovery model and both database and log file gets backed up, doesn't this truncate the log file and would this mean that those transactions truncated from the log file would then become unavailable to the SQL log reader tool?
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36487736
use xp_readerrorlog command in the management studio, you will get the error log
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36488139
>>use xp_readerrorlog command in the management studio, you will get the error log<<
Pray tell what in the world has that got to do with the Transaction Log?
 
0
 

Author Closing Comment

by:irb56
ID: 36528012
Thanks for your help. :-)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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