SQL Server Portal

Last day of the last year, I came across a situation where I was stuck for a while even though we had backups. However later on found a solution and felt so excited ;).
Note : Please do not use this script for any negative purpose.

Problem :
As I mentioned earlier that last year on 31st Dec 2013, we were busy helping our client for yearend closing and suddenly we got an error from year closing procedure. Given below are the details.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘tbl_Yeartodate_Value’.

Now, the problem is this table gets updated every evening and we do not why it is not available in the database. We do have each and everyday backup. However, we do not know when it was dropped and who dropped it. We started looking for the solution because it is not possible to restore all backups and check one by one.

Solution :
Some of our support engineers proposed the third party solution to recover the table directly because we do not know in which backup this table exists. I bought some time from the client and started doing my development for recovery and after few hours, I developed a script that could give the dropped object name along with the dropped date and dropped by name as well. However, I made sure that the client will not take any action against the person who dropped it.

Let me create few objects and drop it to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--Create Schema
CREATE SCHEMA HumanResources
GO

--Create Table
CREATE TABLE HumanResources.[tbl_Employee](
[Sno] [int] NOT NULL,
[ID] nvarchar(6) Not NULL ,
[name] [varchar](50) NOT NULL,
[Date of Birth]  datetime not null,
[Weight] [int] NULL)

GO

--Create View
Create View HumanResources.[vw_Employee]
as
Select * from HumanResources.[tbl_Employee]
GO

--Create Procedure
Create Procedure HumanResources.[usp_Employee]
@EmployeeID nvarchar(6)
as
Select * from HumanResources.[tbl_Employee] Where [ID] =@EmployeeID
GO

--Create function
Create FUNCTION HumanResources.[fn_Employee](@EmployeeID nvarchar(6))
RETURNS int
AS
Begin
Declare @Weight int
Select  @Weight = [Weight]
from HumanResources.[tbl_Employee] Where ID =@EmployeeID
Return  @Weight
End
GO

--Create Trigger
CREATE TRIGGER HumanResources.trg_Employee
ON [tbl_Employee]
FOR INSERT
AS RAISERROR (50009, 16, 10)
GO

--Drop all objects
DROP TRIGGER HumanResources.trg_Employee
DROP VIEW HumanResources.[vw_Employee]
DROP PROCEDURE HumanResources.[usp_Employee]
DROP FUNCTION HumanResources.[Fn_Employee]
DROP TABLE HumanResources.[tbl_Employee]
GO

Once you dropped all object, you need to create the given below script to recover who dropped what object at what time.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP PROCEDURE Recover_Dropped_Objects_Detail_Proc
--GO
CREATE PROCEDURE Recover_Dropped_Objects_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

;WITH CTE AS (Select B.name AS [Schema Name]
,REPLACE(SUBSTRING(A.[RowLog Contents 0],14,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Object Name]
,[Transaction ID]
,A.[RowLog Contents 0]
FROM fn_dblog(NULL,NULL) A
LEFT JOIN sys.schemas B
ON CONVERT(INT,SUBSTRING([RowLog Contents 0],2,2))= B.schema_id
WHERE A.[AllocUnitName] ='sys.sysschobjs.nc1'AND
A.[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] LIKE '%DROP%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))

SELECT
[Schema Name]
,[Object Name]
,B.[Begin Time]  AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name]LIKE '%DROP%'
INNER JOIN sys.sysusers  C ON B.[Transaction SID]=C.[Sid]
GO

Once you created the above stored procedure, lets execute it and get all the object names dropped during a particular interval, along with its dropped date and dropped by name.
Example :

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
EXEC Recover_Dropped_Objects_Detail_Proc  '2014/02/26','2014/02/26'
GO
--OUTPUT

Let me know if you came across this issue and its solution as well.

Read Full Post »