
Have you ever tried to delete an object from the database by mistake or other error? You can undelete object – sometimes.
Then you should read on in this short post.
I recently came across a good co-worker of mine who lost one of the views on the developer database. He called me for help.
Fortunately the database was in FULL RECOVERY mode – so I could extract the object from the database log and send the script to him for his further work that day. I think I saved him a whole day of work…
The undelete object script
Here is the script I used:
select convert(varchar(max),substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) as [Script] from fn_dblog(NULL,NULL) where 1=1 and [Operation]='LOP_DELETE_ROWS' and [Context]='LCX_MARK_AS_GHOST' and [AllocUnitName]='sys.sysobjvalues.clst'