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