DatabaseDevelopmentScriptSQL 2012SQL 2014SQL 2016T-SQL

Undelete object from database


Have you ever tried to delete an object from the database by mistake or other error?

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…

Here is the script I used:

	convert(varchar(max),substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) as [Script]
where 1=1
	and [Operation]='LOP_DELETE_ROWS' 
	and [Context]='LCX_MARK_AS_GHOST'
and [AllocUnitName]='sys.sysobjvalues.clst'


Leave a Reply

Your email address will not be published. Required fields are marked *