Getting Number of Records Modified

[itismesa]

Hi all

My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?

please some one guide me how to do that?

Your help is highly appreciated

[skhanal]

Nothing from SQL Server allows you to do that unless you have turned on C2 auditing.

However you can use commercially available tool from Lumigent (www.lumigent.com) called Log Explorer to go through transaction log (live or backup) to see what happened.

Disclaimer: I don't get any commission from Lumigent (I wish though ) , it's a tool that I recommend.

[MAK]

Yes and No.

You need to use third party tool like Log Explorer and old backup, tranlog files.

[MAK]

WOW. Skhanal you beat me.

hehehehe

[rmiao]

If your db is in simple recovery model, even Log Explorer can't help you. If the db is in full recovery model, you need all log backups made in September to get what you need.

[skhanal]

If it is in simple recovery mode then you can restore beginning of September and end of September databases and use data compare from Redgate software to compare data.

Wow, I feel like a software salesman today.

rmiao, now don't say what if he does not have a backup .

[itismesa]

Thank u guys
No it is not in simple recovery mode
So I understand that it can not be done directly from SQL Server. I will need to get a third party program like Log Explorer.
Correct me if i am mistaken

[MAK]

If you are not worried about details of the records try this (after restoring the backup)

dbcc log (2)

and look into the column "Operation"

[rmiao]

That only gives you whatever in current log since last log backup.