SQL Server Portal

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

Read Full Post »