Check who deleted entry from msdb..restorehistory table

Question

Check who deleted entry from msdb..restorehistory table

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
January 31, 2017 at 1:44 pm

#318255

Hello Experts
I found that someone had deleted few random entry from msdb..resorehistory table. Is there anyway I can find out who deleted the entries? Note: The most recent ever entry was removed and older all entries are there, so apparently it is not possible that the purge job has executed to clear latest entry, keeping the oldest entries.
Kindly suggest
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

SQL-DBA-01 - Tuesday, January 31, 2017 1:44 PM
Hello Experts
I found that someone had deleted few random entry from msdb..resorehistory table. Is there anyway I can find out who deleted the entries? Note: The most recent ever entry was removed and older all entries are there, so apparently it is not possible that the purge job has executed to clear latest entry, keeping the oldest entries.
Kindly suggest

If it were me, I would email the handful of fellow DBAs who are SYSADMIN and simply ask who did this and why. If you have no idea who could have done this, then that indicates a deeper problem regarding the need to restrict who has access to the admin role.

Anyhow, if there is no event trace or audit trigger on on delete operations, then the other other method I can think of at the moment to discover who deleted from a specific object, would be to query meta-data from the transaction log using fn_dblog.
http://dba.stackexchange.com/questions/4269/how-to-find-out-who-deleted-some-data-sql-server

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

SQL-DBA-01 - Tuesday, January 31, 2017 1:44 PM
Hello Experts
I found that someone had deleted few random entry from msdb..resorehistory table. Is there anyway I can find out who deleted the entries? Note: The most recent ever entry was removed and older all entries are there, so apparently it is not possible that the purge job has executed to clear latest entry, keeping the oldest entries.
Kindly suggest

How did you figure that out? Are you sure they're not missing simply due to failures to restore? Are you sure that someone/something isn't dropping the restore history prior to doing the restore because the DB was dropped prior to the restore?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 3

SQL-DBA-01 - Tuesday, January 31, 2017 1:44 PM
Hello Experts
I found that someone had deleted few random entry from msdb..resorehistory table. Is there anyway I can find out who deleted the entries? Note: The most recent ever entry was removed and older all entries are there, so apparently it is not possible that the purge job has executed to clear latest entry, keeping the oldest entries.
Kindly suggest

You sure its someone who did this? Check whether you deleted any databases recently, deleting the DB deletes its history by default unless you uncheck that from SSMS.