Know How to Recover Deleted Table in SQL Server 2012 Without Backup

Summary: Do you want to know how to recover the deleted table in SQL Server 2012 Without Backup. Read this article to recover deleted database objects by using efficient and  Quick solutions.

On occasion, a client may perform UPDATE operation or DELETE operation in SQL Server database without applying the WHERE condition. This is a typical explanation behind experiencing loss of data from SQL Server tables. As the SQL Server database is an exceptionally mainstream relational DBMS among corporate divisions and organizations, the data loss issue amplifies much more. So, clients ought to know about the strategies to recover deleted data from the SQL Server Table if there should arise an occurrence of any disasters.

Deleted rows can be recovered by using LSN if the deletion time is known. This should be possible using Log Sequence Numbers (LSNs). LSN is a special identifier given to each record present in the SQL Server transaction log. We will talk about the procedure to recover deleted SQL Server data and tables with the assistance of Transaction Log and LSNs.

Suggested: For dodging the long and incorrect manual LSN way to deal with recover deleted records from SQL table using a propelled third-party tool, for example, the SQL Database Recovery tool.

Procedure to Know How to Recover Deleted Table in SQL Server 2012 Without Backup

There are a few requirements to be satisfied before we start the procedure to recover deleted data from the SQL table. To effectively recover deleted rows from a table in the SQL Server database, it must have the BULK-LOGGED or FULL recovery model when the deletion originally happened. Some brisk activities are required so the logs are as yet accessible to play out the recovery of data.

Follow the steps offered below to recover deleted data from SQL Server 2005, 2008, 2012, 2014, and 2016 by the use of transaction logs.

Step 1

Check the number of rows present in the table from which the data has been inadvertently deleted utilizing the underneath referenced question:

SELECT * FROM Table_name

Step 2

Take the transaction log backup of the database using the query given beneath:

USE Databasename

GO

Backup LOG [Databasename]

TO DISK = N’D:\Databasename\RDDTrLog.trn’

WITH NOFORMAT, NOINIT,

NAME = N’Databasename-Transaction Log Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 3

So as to recover deleted data from SQL Server Table, we have to gather some data about the deleted rows. Run the query offered underneath to accomplish this reason

USE Databasename

GO

Select [Current LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_DELETE_ROWS’

From the query given above, we will get the Transaction ID (Let’s say 000:000001f3) of the deleted rows. Presently, when these rows were deleted is to be resolved to use this ID.

Step 4

In this progression, we will locate the particular time at which rows were deleted using the Transaction ID 000:000001f3. This is finished by executing the inquiry given as follows:

USE Databasename

GO

SELECT

[Current LSN], Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f3’

AND

[Operation] = ‘LOP_BEGIN_XACT’

On executing this inquiry we will get the estimation of current Log Sequence Number (LSN) (suppose 00000020:000001d0:0001).

Step 5

Presently we will begin the restore procedure to recover deleted data from SQL Server Table rows that were lost. This is finished using the underneath query:

USE Databasename

GO

Restore DATABASE Databasename_COPY FROM

Plate = ‘D:\Databasename\RDDFull.bak’

WITH

MOVE ‘Databasename’ TO ‘D:\RecoverDB\Databasename.mdf’,

MOVE ‘Databasename_log’ TO ‘D:\RecoverDB\Databasename_log.ldf’,

REPLACE, NORECOVERY;

GO

 Step 6

Presently apply transaction log to restore deleted rows by utilizing LSN 00000020:000001d0:0001:

USE databasename

GO

Restore LOG Databasename_COPY FROM DISK = N’D:\Databasename\RDOTrLog.trn’ WITH STOPBEFOREMARK = ‘lsn:0x00000020:000001d0:0001’ Note: Since LSN values are in Hexadecimal structure and for restoring tables utilizing this LSN, we have to change over it into the decimal structure. For this reason, we include 0x not long before the LSN as appeared previously.

Step 7

The procedure to recover deleted records from the SQL table will get finished effectively. Presently check whether the deleted records are back in the database named Databasename_Copy.

USE Databasename_Copy GO Select * from Table_name

 Limitations of Transaction Log Approach

• Profoundly tedious strategy to recover deleted data from SQL Server Table, as it includes a few extensive queries to be executed.
• Amazingly mind-boggling to actualize for clients not having sufficient specialized data.
• More prominent odds of losing data because of mistakes while application and execution of queries.

Elective Solution to Know How to Recover Deleted Table in SQL Server 2012 Without Backup

The best elective solution is accessible to recover rows from SQL Server 2019/2017/2016/2014/2012/2008/2005. The elective solution that we talk about here can resolve every single kind issue that emerges in the SQL Server. It can fix SQL errors and fix SQL MDF and NDF files. FreeViewer SQL Recovery Tool is a business tool that permits recovering Triggers, Rules, Functions, Tables. Moreover, it additionally restores Stored Procedures in SQL Server effectively with no data loss. The significant part is, the application gives many propelled options while fixing corrupted SQL Server database files. Additionally, this application shows the deleted SQL table records in red color. This product is good with Microsoft Outlook 2019 and all its beneath versions.

Download the free demo release to comprehend the best possible capacity of the application.

Steps to Recover Deleted Database Objects Easily

1. Download and dispatch the  SQL Recovery tool, and open the MDF file.
2. Select Advance Scan alternative, Detect the SQL Server version to recover deleted records in SQL Server.
3. The tool will start the Scanning procedure of the primary database file (MDF)
4. See the SQL Database Components, the tool shows the deleted SQL table records in red color. After that click on the Export
5. You have to give the Database authentication details in the next segment.
6. Check the ‘Create New Database’ option and give a Database Name.
7. Fare the Database ‘With schema and data’ in order to recover data in the deleted rows in the SQL Server. (Also check the checkbox Export deleted records)
8. Presently save the report and open the recently made database which contains Recovered records.

Conclusion

Also Read: The user can also read another similar post to know Microsoft SQL Server Disaster recovery best practices.

Although the LSN strategy can restore deleted records from SQL tables, it’s anything but a prescribed choice for clients because of its multifaceted nature and repetitiveness. Rather, it is encouraged to use an automated solution to Know How to Recover Deleted Table in SQL Server 2012 Without Backup.