We had a database deleted from our SQL Server 2000. We were able to do a
restore and fortunatley did not loose anything. We checked SQL logs and Even
t
logs and find no record of the deletion. Is it possible to log deletion of
databases? Is there somewhere else that we can look to see when and how it
got deleted?
Thank You
TomIf you were not running a profiler trace at the time then the only real
way is to trawl through the transaction log for the master database,
assuming your master DB is not in SIMPLE recovery mode, from the time
the database was dropped. There are 3rd party tools to interpret the
transaction log (like Lumigent Log Explorer for example). There's also
an undocumented system function called ::fn_dblog(<FirstLSN>,<LastLSN> )
that will display the current transaction log (between the LSNs you
specify - use null instead of a LSN to not limit the range) but the
output of the function is not very user friendly and takes a fair bit of
scrutinising to figure out what it means. I'd be looking for
LOP_DELETE_ROWS operations on the dbo.sysdatabases table - you might be
able to figure it out from that. Chances are, however, that you'll
waste hours on a wild goose chase, but good luck.
*mike hodgson*
http://sqlnerd.blogspot.com
TomD wrote:
>We had a database deleted from our SQL Server 2000. We were able to do a
>restore and fortunatley did not loose anything. We checked SQL logs and Eve
nt
>logs and find no record of the deletion. Is it possible to log deletion of
>databases? Is there somewhere else that we can look to see when and how it
>got deleted?
>Thank You
>Tom
>|||If you were not running a profiler trace at the time then the only real
way is to trawl through the transaction log for the master database,
assuming your master DB is not in SIMPLE recovery mode, from the time
the database was dropped. There are 3rd party tools to interpret the
transaction log (like Lumigent Log Explorer for example). There's also
an undocumented system function called ::fn_dblog(<FirstLSN>,<LastLSN> )
that will display the current transaction log (between the LSNs you
specify - use null instead of a LSN to not limit the range) but the
output of the function is not very user friendly and takes a fair bit of
scrutinising to figure out what it means. I'd be looking for
LOP_DELETE_ROWS operations on the dbo.sysdatabases table - you might be
able to figure it out from that. Chances are, however, that you'll
waste hours on a wild goose chase, but good luck.
*mike hodgson*
http://sqlnerd.blogspot.com
TomD wrote:
>We had a database deleted from our SQL Server 2000. We were able to do a
>restore and fortunatley did not loose anything. We checked SQL logs and Eve
nt
>logs and find no record of the deletion. Is it possible to log deletion of
>databases? Is there somewhere else that we can look to see when and how it
>got deleted?
>Thank You
>Tom
>
No comments:
Post a Comment