[RESOLVED]Read Log Files

[erickwidya]

is there any way to read Log file for past days?

because one person delete an invoice and we need prove that he did that so he can be processed by the law..

thanks

[Hack]

Log files from what kind of database?

[kaffenils]

There are no MS shipped tools to do this, but 3rd part products exists. For example http://www.lumigent.com/products/le_sql.html

[szlamany]

If your app/server needs this level of security there are other third party products that track all queries against the database.

[erickwidya]

Log files from what kind of database? -> sorry forget to mention that we used SQL Server 2005

so we must choose that 3rd party then..ok..thanks for the info

i'll inform that

[erickwidya]

following this issue

i've tried apexsql but it seem empty or i'm doing it wrong

i restore the backup db to the trouble day (it's backup daily), run the apexsql then the result empty

does anyone ever do this? thx

[erickwidya]

or can i do something like this

(ie: trouble day are 2 dec 2007)
restore db to one day before the trouble (1 Dec 2007), restore the db with the 4 Dec 2007 data, so it will have transaction log between 1 to 4 dec 2007 and i can use the apexsql..

can sql do something like that? or any idea?

[szlamany]

Do you have the backup from 1 Dec - it's a full backup - right?

And then next backup you have is 4 Dec - right? Also a full backup? Or a transaction log backup?

[erickwidya]

i think it is always use a full backup

what's the difference?

[szlamany]

Well...

The difference is huge - you can read for hours on the MSDN site.

I was hoping to direct you to RESTORE with STOPATMARK

http://msdn2.microsoft.com/en-us/library/ms191459.aspx

Which can be both LSN and time related.

Figuring we could get down to the moment of the suspected delete - even down to the LSN of the suspected delete.

But I believe that this is only possible with LOG restores.

I believe - and I could be very wrong - that a FULL BACKUP flushes the transaction log of any and all "committed" transactions so that only the DB and those transactions that are "open" are brought into the backup set.

[erickwidya]

I believe - and I could be very wrong - that a FULL BACKUP flushes the transaction log of any and all "committed" transactions so that only the DB and those transactions that are "open" are brought into the backup set. -> is this something like if u do the Full Backup then all that record is the current transaction to the last time the Backup occured so if u want the previous days, u'll found nothing?

coz i'm trying do the the full backup but the transaction i created is not found unless i'm using the TransactionLog backup

[erickwidya]

I believe - and I could be very wrong - that a FULL BACKUP flushes the transaction log of any and all "committed" transactions so that only the DB and those transactions that are "open" are brought into the backup set. -> is this something like this

the Full backup only 'write' transactions from the last time it backed up to current transaction?

coz i've been trying to read it but it found nothing unless i'm using Log backup

[szlamany]

coz i'm trying do the the full backup but the transaction i created is not found unless i'm using the TransactionLog backup

yes - I think that might be the case.

After the fact trying to chase down the perpertrator of the delete is tough.

Having third party app that tracks log and query actions against DB in place prior to the loss is different.

Remember the transaction log is a database tool - required by the engine to track changes to data - not who - but simply changes related to transactions. There might be a PROCESS ID stored in the LSN record - but even determining who that is might be close to impossible after a month.

[erickwidya]

ok then

thanks for the info [sz]

i'll inform if i found something about it..

[erickwidya]

Each time the transaction log is backed up, SQL Server removes all of the committed transactions in the log and writes them to the backup media. Due to this incremental process, transaction logs are not cumulative and we must maintain a complete set of transaction logs reaching back to the most recent full or differential backup

http://databases.about.com/od/sqlser...disaster_3.htm

[Hack]

So, is this resolved for you or do you still have questions?

[erickwidya]

sorry for late reply, yes i think i have no further question since after this days..it's being state that we must use FULL RECOVERY MODEL if we want to read the log files

thanks all