SQL Server Troubleshooting: How to recover views and procedures dropped by mistake
When you have deleted views and procedures by accident try this:
SELECT Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
FROM fn_dblog(DEFAULT, DEFAULT)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
If you want to try, just:
create view Technet as
select 1 as one,2 as two,3 as three,4 as fourth
drop the view:
drop view technet
and, let's try also with a procedure:
create procedure forum as
declare @st int
set @st=1
print @st
drop the procedure:
drop procedure forum
Just execute the code:
SELECT Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
FROM fn_dblog(DEFAULT, DEFAULT)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
And you can see both in your result.