locked
Recovering a deleted view. How? RRS feed

  • 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

    Tuesday, February 16, 2016 4:28 PM

Answers

  • 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

    • Marked as answer by DIEGOCTN Tuesday, February 16, 2016 4:58 PM
    Tuesday, February 16, 2016 4:58 PM

All replies

  • From your database backup or rewrite it.
    • Proposed as answer by Naomi N Tuesday, February 16, 2016 4:46 PM
    Tuesday, February 16, 2016 4:43 PM
  • 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

    Tuesday, February 16, 2016 4:49 PM
  • 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.

    Tuesday, February 16, 2016 4:55 PM
  • 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

    • Marked as answer by DIEGOCTN Tuesday, February 16, 2016 4:58 PM
    Tuesday, February 16, 2016 4:58 PM
  • 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.

    Tuesday, February 16, 2016 5:00 PM
  • It does not make sense to recover objects not from backups. It wastes a lot of valuable time to try to figure it out.
    Tuesday, February 16, 2016 5:16 PM
  • 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..
    • Edited by DIEGOCTN Tuesday, February 16, 2016 7:00 PM
    Tuesday, February 16, 2016 7:00 PM
  • 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.


    Tuesday, February 16, 2016 8:14 PM
  • 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

    • Edited by Eric__Zhang Wednesday, February 17, 2016 4:54 AM
    • Proposed as answer by Naomi N Wednesday, February 17, 2016 4:56 AM
    Wednesday, February 17, 2016 4:54 AM