Recovering a deleted view. How?

Question

Hi guys, I just deleted now a view (right click --> delete) by mistake. How can I recover the view? Is there a way?

Many thanks

Answer 1

From your database backup or rewrite it.

Answer 2

If you have a Source Control with the code for the View, you can re-create it from that script.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 3

I found out a good script. No backup (it would have been too old), no rewrite (it would have been crazy, I didn't remember how I was able to write that big view).

Thanks anyway.

Answer 4

The code:

Create PROCEDURE Recover_Dropped_Objects_Proc
@Database_Name NVARCHAR(MAX),
@Date_From DATETIME='2016/01/01',
@Date_To DATETIME ='2016/12/31'
AS
 
DECLARE @Compatibility_Level INT
SELECT @Compatibility_Level=dtb.compatibility_level
FROM
master.sys.databases AS dtb WHERE dtb.name=@Database_Name
 
IF ISNULL(@Compatibility_Level,0)<=80
BEGIN
    RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
    RETURN
END
 
Select Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
from fn_dblog(NULL,NULL)
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
And [AllocUnitName]='sys.sysobjvalues.clst'
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL) 
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')  
And [Transaction Name]='DROPOBJ'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0
GO
 
--Execute the procedure like
--EXEC Recover_Dropped_Data_Proc 'Database Name'
 
----EXAMPLE #1 : FOR ALL Dropped Objects
EXEC Recover_Dropped_Objects_Proc 'test'
--GO
------EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Dropped_Objects_Proc 'test','2011/12/01','2013/01/30'
--RESULT

Answer 5

IF the view was recently created or altered, you could try:

SELECT text
  FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
 ORDER BY deqs.last_execution_time DESC

However, there's only a limited amount of data there.

Often, the text is parameterized too, so you may need to clean it up.

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

Answer 6

It does not make sense to recover objects not from backups. It wastes a lot of valuable time to try to figure it out.

Answer 7

It does not make sense to recover objects not from backups. It wastes a lot of valuable time to try to figure it out.

I do not understand your reply Li. I didn't have a recent backup. I guess that was the best way to retrieve a deleted view, anyway. Without trying to restore any backup..especially, and not only, for the time that you need to spend to restore a big backup. But maybe I am wrong and I am missing something..

Answer 8

If a database is important, you should always have a working backup plan in place, even it is a development database. You may have a need to get back some data back besides an object. I don't have any backup for one of my instances but I can afford losing that whole thing.

Another option is to have version control for your database as a database project to maintain the database versions in a source control system.

Answer 9

Hi DIEGOCTN,

That's really a cool stored procedure. However it won't work 100%.

I see the sp relying on the undocumented fn_dblog, which reads the active transactions in the virtual log files. Say, after a checkpoint, you may most probably not find it any longer. So you're lucky this time but not next time.

You can use the sp in emergency， when it doesn't work, restoring database can be the only option. To re-write it, you can restore the database to a test enviroment and query the definition from sys.sql_modules. It won't affect the production server.

---

Eric Zhang
TechNet Community Support