Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to recover the accidentally renamed object name’

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

recover renamed object name

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
Advertisements

Read Full Post »