Recovery table data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Recovery table data

Author

Topic

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-04-25 : 09:12:06

I have an important table, I don't how it table data delete/remove ?

I see the table data is empty how it empty ?

structure is there.

Please how can Recovered the table data ?

How can find the what reasons table data delete/remove ?
How can open the sql server log file ?
log file only show the database start , recovery complete etc..

Please help to recover the table data and reasons ?

Mateen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 09:48:09

you can recover by restoring your backup

you can investigate by narrowing down the users otherwise if you really have no idea when this occured (like within 24 hours), i suggest you try lumigent log explorer and probably get an idea who did it or atleast what account was used

HTH

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-25 : 10:18:59

"you can recover by restoring your backup"

... you can restore the backup to a new, temporary, database and then "copy" the data from that temporary database into the "live" database.

If you are using TLog backups you should restore to a point-in-time just before the deletion (trial and error to find when that was, I expect), otherwise you will have to use whichever backup precedes the accidental deletion, and then you will be missing any data AFTER the backup but BEFORE the deletion accident.

Kristen

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-04-25 : 10:19:37

Thanks for your response.

lumigent log explorer is in sql server enterprise manager ?
where is location ?

or it is separate utility ?

Tlog backup means ?

I have normal backup. But my problem is that I have old backup.
in old backup I don't have my table data informations.

there any way that I can recover my table data ?
I don't it is accidental deletion.

I can find how data delete / remove from the table ?
or how can find the reasons ?

Mateen

Kristen
Test

22859 Posts

Posted - 2006-04-25 : 11:08:24

"lumigent log explorer is in sql server enterprise manager ?"

No, its a 3rd party tool, but I believe they have a Demo Pack that you can download.

"Tlog backup means ?"

That in additional to a periodic full backup you are also making Transaction Log backups, and your database Recovery model is set to FULL, and not to SIMPLE. This is the Default - so you may well be!

If you are not making TLog backups, but the Recovery Model is set to FULL then you should be able to make a TLog backup now, and then restore [to a Temporary Database] the last Full backup before the accidental deletion and then the TLog Backup (using point-in-time restore to just before the accident).

If you are not using FULL Recovery Model then some sort of Log Reader, like lumigent, is the only route. And sort out your backup strategy pronto so that you are safeguarded in the future

Kristen

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-04-26 : 05:45:29

Thanks for response and comments.

Backup is most important.
But some time backup miss, and meanwhile somebody / accidentally
lost the data from table. there should be some information store
how data lost or which user data lost by mistake or accidentally.
at lest find the reasons of data lost.

I try to find/download the lumigent log explorer.

Mateen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 06:16:54

then you will not to integrate some auditing

backup just ensures that you can restore to a certain state

the audit is different, this tells you who, where, when and what was used and takes time to build

--------------------
keeping it simple...

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2006-04-26 : 10:30:14

I make trigger. by the asp program any user insert/update/delete
the records it records in audit trigger table.

1. if user access sql server by enterprise manager or sql query anaylzer, and delete the records
how can audit this ?

2. and if user delete trigger of table in enterprise manager
can audit this ?

Mateen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 11:05:33

1. if coming from asp, you can add some audit trails called from the apps
2. restrict the developers from direct access to the tables

if you're still interested in triggers, something like this


create trigger trgname on owner.tablename
for insert
as

insert into audit_table(fields.....)
select fields... from inserted

go

create trigger trgname1 on owner.tablename
for update, delete
as

insert into audit_table(fields.....)
select fields... from deleted

--edit
you can add additional fields in your audit_table like machine name, account used, time the change or attempt occured, etc...

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-26 : 15:35:31

Jen I respectfully suggest you don't need (1) [the INSERT trigger], 'coz that data is in the actual table, you just need (2) [the DELETE trigger], and that saves a significant amount of disk space!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 20:59:43

there are cases that you may want to know who did the insert if via enterprise manager or QA bypassing the apps

an example would be some transactions being inserted directly into the table bypassing any form of validations and cross-checking that should have been accomplished in the apps level

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-04-27 : 01:44:42

Ah Good point! We store that in the main table, but I suppose its just possible that not everyone does that!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources