Today, I was reading Pinal Dave Blog and found “SQL SERVER – Recover the Accidentally Renamed Table” very interesting. I would like to add one more solution to recover accidentally renamed table via SQL Server Log.
Note : It will only recover the table name if you rename it through SSMS.
Lets create a table for example and rename it through SSMS.
Create Table Table_test ([SNO] int) GO
After that, I renamed it to “Table_test2”, “Table_test3″,”Table_test4” respectively via SSMS.
Lets create the procedure for recovery as given below:
CREATE PROCEDURE Recover_Rename_Table_Name_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
Select REPLACE(Substring(A.[RowLog Contents 0],14,LEN(A.[RowLog Contents 0])),0x00,0x) as [Table Name Before Rename]
,REPLACE(Substring(B.[RowLog Contents 0],14,LEN(B.[RowLog Contents 0])),0x00,0x) as [Table 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
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_Rename_Table_Name_Proc 'yyyy/MM/dd','yyyy/MM/dd'
EXEC Recover_Rename_Table_Name_Proc '2012/10/08','2012/10/09'
In case, if you do not know the modified date & time of the table, you can write given below query and use [modify_date] column.
Select [Name],[modify_date] from sys.tables
SQL Server Portal 
Only word I have is Excellent!
[…] 6: Interesting Solution by Muhammad Imran I suggest you to go ahead and read it […]
Excellent post, need to really appreciate it !!!
Regards,
Girijesh
Great Post, truly appreciate it.!
This doesn’t work for me on SQL2008R2 SP1.
Hi Andrew,
Thank you for your feedback, I tested this script in SQL Server 2008 R2 SP1 and it is working fine, Can you please check that these where clauses/ joins match in your fn_dblog data ?
Imran
I’ve tried again with a new database, and I get 2 rows with a single character “T” in both columns. The values of a.[Rowlog Contents 0] and b.[Rowlog Contents 0] are:
0x260001000000E7A4787D010022005400610062006C0065005F007400650073007400
and
0x260001000000E7A4787D010024005400610062006C0065005F0074006500730074003200
Seems to be a problem with the SUBSTRING or REPLACE commands as it’s truncated the string to “T”. I’ve checked the query and copied it from the source option.
I can get it to work on SQL2005 SP4, but have to comment out the line
–A.AllocUnitId IN (562949955649536)
The A.AllocUnitId is 72057594047037440 or 72057594047102976.
In SQL2008R2 SP1 the A.AllocUnitId is 562949955649536
Correction to previous comment, a.[Rowlog Contents 0] and b.[Rowlog Contents 0] are:
0x260001000000E7A4787D010024005400610062006C0065005F0074006500730074003200
and
0x260001000000E7A4787D010024005400610062006C0065005F0074006500730074003300
Hi Andrew,
Can you please try this and check the results.
eclare @varbinary as varbinary(Max)
Set @varbinary=0x260001000000E7A4787D010024005400610062006C0065005F0074006500730074003200
–Select LEN(@varbinary)
–Select Substring(@varbinary,14,LEN(@varbinary))
Select REPLACE(Substring(@varbinary,14,LEN(@varbinary)),0x00,0x) as [Refore Rename]
Set @varbinary=0x260001000000E7A4787D010024005400610062006C0065005F0074006500730074003300
–Select LEN(@varbinary)
–Select Substring(@varbinary,14,LEN(@varbinary))
Select REPLACE(Substring(@varbinary,14,LEN(@varbinary)),0x00,0x) as [After Rename]
I stopped the query after it ran for 5 minutes – varbinary(max) – LEN(@varbinary) returns 36, Substring returns
0x005400610062006C0065005F0074006500730074003200
but REPLACE(Substring(@varbinary,14,LEN(@varbinary)),0x00,0x) causes the query to run without completion, tried on multiple servers.
When changing @varbinary from varbinary(max) to varbinary(8000), I get value “T”.
Hi Andrew,
I will create a same environment and test it. Can you please post further details of SQL Server environment like collation etc.
Thank you.
Imran
Collation: Latin1_General_CI_AS
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULL_DFLT_ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE DATABASE [TESTME] ON PRIMARY
( NAME = N’TESTME’, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TESTME.mdf’ , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
LOG ON
( NAME = N’TESTME_log’, FILENAME = N’L:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\TESTME_log.ldf’ , SIZE = 20480KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [TESTME] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [TESTME].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [TESTME] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TESTME] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TESTME] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TESTME] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TESTME] SET ARITHABORT OFF
GO
ALTER DATABASE [TESTME] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TESTME] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TESTME] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TESTME] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TESTME] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TESTME] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TESTME] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TESTME] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TESTME] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TESTME] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TESTME] SET DISABLE_BROKER
GO
ALTER DATABASE [TESTME] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TESTME] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TESTME] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TESTME] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TESTME] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TESTME] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TESTME] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TESTME] SET READ_WRITE
GO
ALTER DATABASE [TESTME] SET RECOVERY FULL
GO
ALTER DATABASE [TESTME] SET MULTI_USER
GO
ALTER DATABASE [TESTME] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TESTME] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N’TESTME’, N’ON’
GO
Thank you Andrew. Will test and update you.
Imran
[…] 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. […]
[…] 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. […]
Saved my day !!!Awesome