How to recover the deleted stored procedure, function, trigger, view

In this article, we will learn how to recover views, stored procedures, functions & triggers via SQL server log.

Step 1 :
Lets create few objects to explain the recovery process.

CREATE TABLE [dbo].[Student](       [Sno] [int] NOT NULL,       [Student ID] nvarchar(6) Not NULL ,       [Student name] [varchar](50) NOT NULL,       [Date of Birth]  datetime not null,       [Weight] [int] NULL)   GO Create View Vw_Student as Select * from [Student] GO Create Procedure SP_Student @StudentID nvarchar(6) as Select * from Student Where [Student ID] =@StudentID GO Create FUNCTION [dbo].[Fn_Student](@StudentID nvarchar(6)) RETURNS int AS Begin     Declare @Weight int     Select  @Weight = [Weight]         from Student Where [Student ID] =@StudentID     Return  @Weight End GO CREATE TRIGGER trg_Student ON Student FOR INSERT AS RAISERROR (50009, 16, 10) GO

Step 2:
Lets drop these objects.

Drop View [dbo].[Vw_Student] GO Drop Procedure [dbo].SP_Student GO Drop Function [dbo].[Fn_Student] GO Drop Trigger [dbo].[trg_Student] GO

Step 3:
Check the existence of these objects to make sure that objects are dropped properly.

Select * from [Vw_Student] GO EXEC SP_Student 1 GO Select dbo.[Fn_Student](1)

Step 4:
Create the given below stored procedure to recover the dropped objects.

-- Script Name: Recover_Dropped_Objects_Proc -- Script Type : Recovery Procedure -- Develop By: Muhammad Imran -- Date Created: 04 Dec 2012 -- Modify Date: -- Version    : 1.0   Create PROCEDURE Recover_Dropped_Objects_Proc @Database_Name NVARCHAR(MAX), @Date_From DATETIME='1900/01/01', @Date_To DATETIME ='9999/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