Prev: Lock pages in memory greyed out
Next: To Shrink or Not to Shrink?
From: Pat on 3 May 2010 18:08
Hi,

I am using SQL 2005. The database backup routines are: 1) Daily full backup
(.bak file) at 1 AM. 2) Transaction log backup (.trn files) every 15 min from
8 AM - 8 PM from Monday to Sunday. Let's say the database is damaged (e.g.,
by mistake, a table was dropped, or some rows in a table were deleted, or the
database just does not work with unknown reason). This problem is found out
on Friday at 11 AM. Now, how do you trace the database what had happened and
what exact date and time the incident occurred? I want to know how to
diagnosis the problem.

Pat
From: Geoff Schaller on 3 May 2010 18:19
Pat,

You have an interesting concept of 'damage' :-).

Deletion of tables, rows or any other object is perfectly normal so you
would need to define the processes that cause your 'damage' and watch
for them explicitly. Your best option would be a trigger on those tables
which deleted the rows (or details of the event) to an audit table. You
can have database level triggers to detect and record table level
changes. I presume you have the usual range of PK and FK objects?

But SQL Server itself is not going to complain about a table or two
missing. Why should it care? You need to define the table and row rules
and design your tools accordingly.

Geoff Schaller
Software Objectives


"Pat" <Pattt(a)newsgroups.nospam> wrote in message
news:F2F51D81-A2D8-4C07-9020-7FB377CB8216(a)microsoft.com:

> Hi,
>
> I am using SQL 2005. The database backup routines are: 1) Daily full backup
> (.bak file) at 1 AM. 2) Transaction log backup (.trn files) every 15 min from
> 8 AM - 8 PM from Monday to Sunday. Let's say the database is damaged (e.g.,
> by mistake, a table was dropped, or some rows in a table were deleted, or the
> database just does not work with unknown reason). This problem is found out
> on Friday at 11 AM. Now, how do you trace the database what had happened and
> what exact date and time the incident occurred? I want to know how to
> diagnosis the problem.
>
> Pat

From: Simon on 5 May 2010 08:43
Hi Pat,

I think Tibor was referring to a transaction log reader as a third party
tool.

There are certain tools that you can use to retroactively read the SQL
Transaction log. These tools basically allow you to see each operation
as it occured by looking at everything in the transaction log.

I think Red Gate used to have quite a nice one for SQL Server 2000 and I
would be surprised if the likes of Apex and Quest didn't have something
along these lines.

I'm not sure if this would give you the sort of information you are
after - but they certainly can show you any transactions that caused an
entry in the transaction log over a set period of time

HTH

Simon




On 03/05/2010 23:08, Pat wrote:
> Hi,
>
> I am using SQL 2005. The database backup routines are: 1) Daily full backup
> (.bak file) at 1 AM. 2) Transaction log backup (.trn files) every 15 min from
> 8 AM - 8 PM from Monday to Sunday. Let's say the database is damaged (e.g.,
> by mistake, a table was dropped, or some rows in a table were deleted, or the
> database just does not work with unknown reason). This problem is found out
> on Friday at 11 AM. Now, how do you trace the database what had happened and
> what exact date and time the incident occurred? I want to know how to
> diagnosis the problem.
>
> Pat

 | 
Pages: 1
Prev: Lock pages in memory greyed out
Next: To Shrink or Not to Shrink?