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:

1
2
3
4
5
6
7
8


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'

Brian Bønk

Data for everyone, build by humans

Get all the latest news directly in your inbox

Get certified - Fabric DP-600 book

Early release!

Subscribe to my channel

Free Microsoft Workshops

Recent posts

Categories

Tags