In my first article, I wrote about “How to recover the deleted data from SQL Server” from SQL server log. After this article, I received a lot of queries to recover dropped objects like tables, stored procedure, functions & triggers with the help of SQL server Log.
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