Yesterday, I was browsing few objects in SSMS in my test SQL server database and by mistake, I did a single click on an object and typed something and it got renamed and disappered from the search result. Now, what and who I renamed? I wasn’t sure 😦 Fortunately, I had a backup and I could restore and recover my objects. But what if I did not have a backup and it happens on production database ? a nightmare I’d say?
Last year, I developed a tool that usually recovers the accidentally renamed table name but it does not give you the object type. After this incident, I started with my research and modified the script and now it can recover any renamed object with its object type as well.
Note : It will only recover the object name if you renamed it through SSMS.
Given below is the stored procedure that can recover any object name.
--DROP PROCEDURE Recover_Renamed_Object_Name_Proc
--GO
CREATE PROCEDURE Recover_Renamed_Object_Name_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
Select type_desc As [Object Type]
,REPLACE(Substring(A.[RowLog Contents 0]
,14,LEN(A.[RowLog Contents 0])),0x00,0x) as [Object Name Before Rename]
,REPLACE(Substring(B.[RowLog Contents 0]
,14,LEN(B.[RowLog Contents 0])),0x00,0x) as [Object Name After Rename]
FROM sys.fn_dblog(NULL, NULL) A
Inner Join sys.fn_dblog(NULL, NULL) B
On A.[Transaction ID]=B.[Transaction ID]
And A.AllocUnitId = B.AllocUnitId
Left Join sys.objects S On CONVERT(BIGINT,CONVERT(VARBINARY(MAX)
,REVERSE(Substring(A.[RowLog Contents 0],7,4))))=S.object_id
WHERE
A.AllocUnitId IN (562949955649536) AND A.Context IN ('LCX_MARK_AS_GHOST')
AND A.Operation IN ('LOP_DELETE_ROWS') AND B.Context IN ('LCX_INDEX_LEAF')
AND B.Operation IN ('LOP_INSERT_ROWS')
/*Use this subquery to filter the date*/
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]='user_transaction'
AND CONVERT(NVARCHAR(11),[Begin Time])
BETWEEN @Date_From AND @Date_To)
GO
--Example EXEC Recover_Renamed_Object_Name_Proc 'yyyy/MM/dd','yyyy/MM/dd'
EXEC Recover_Renamed_Object_Name_Proc '2013/10/31','2014/12/31'
--OUTPUT
In case you do not know the modified date & time of any object, you can write given below query and use [modify_date] column.
Select name, type_desc, modify_date from sys.objects Order by Modify_date DESC
SQL Server Portal 