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.
SQL Server Portal 