How to Recover Deleted Table in SQL Server Database?
Overview: In this blog post, we will explain the top 3 ways to recover deleted table in SQL Server database. Hence, keep continue reading this write-up and get the effortless solution to restore deleted SQL table records.
Sometimes Microsoft SQL Server users may execute the Delete / DROP command for associated tables. They accidentally deleted SQL table or transaction records from table. But business users never want to down SQL servers because it impacts their businesses.
Know MS SQL Table and Its Commands
We all know that MS SQL Server uses tables to manage its relational database including all records. A lot of organizations and companies use SQL (Structure Query Language) to fulfill their automation transaction needs.
Businesses and e-commerce websites use SQL Server database tables to store preferred automation records i.e., Employee ID, Employee Name, Address, Phone Numbers, Email Id, etc. SQL table schema may be different accordingly.
SQL Administrators mainly perform the mentioned commands against SQL table to manage them accordingly.
- DELETE: for deleting a record.
- DROP: for deleting an entire table including all available records.
- INSERT: for inserting a record in the table.
- UPDATE: for modifying SQL table records.
- CREATE: for creating a new SQL table or object in the SQL database.
- GRANT: for assigning privilege permissions.
- REVOKE: to get back assigned privilege permissions.
Recommended: Multiple Ways to Repair Corrupt MDF Files in SQL Server
#1) Recover Deleted Data from Table in SQL Server by SSMS
Just proceed with the below-mentioned steps to recover deleted table in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000.
- Start SQL Server Management Studio (SSMS) on your computer.
- Thereafter, right-click on the Databases folder and pick the Restore Database
- Check the Radio button associated with your computer and click on the Browse icon (…).
- After that, choose the backup media type as File and press Add
- Select a backup file to restore deleted table from database and hit on Ok.
- Thereafter, your deleted SQL table data is restored and now see the message of completion.
- Lastly, press the Ok button to successfully restore deleted data from SQL Server table.
#2) Restore Deleted Table in SQL Server Using LSN
Note That: LSN (Log Sequence Number) technique will not allow to recover deleted data from Table in SQL server. If the transaction log backup got damaged and missing. This is mandatory to perform this technique because every SQL log record can be identified by an LSN.
1 Phase: Create SQL Database
Perform the following command for creating SQL database named as “RecoverDeletedTables” and table name as “Employees”.
USE [master];
GO
CREATE DATABASE RecoverDeletedTables;
GO
USE RecoverDeletedTables;
GO
CREATE TABLE [Employees] ()
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT ‘City1’);
2 Phase: Insert Data in SQL Table
Using phase 1, we have successfully created SQL database with table including 3 different columns. Now see the insert process.
USE RecoverDeletedTables;
GO
INSERT INTO Employees DEFAULT VALUES;
GO 100
3 Phase: Delete Some Rows in SQL Table
USE RecoverDeletedTables
Go
DELETE Employees
WHERE [Sr.No] < 10
GO
Select * from Employee
4 Phase: Know About Deleted Table Rows
Now, this is time to get information about deleted SQL Rows by searching the transaction log data.
USE RecoverDeletedTables
GO
SELECT
[Current LSN],
[Transaction ID],
Operation,
Context,
AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = ‘LOP_DELETE_ROWS’
After using the above-mentioned command, you can see transaction ids of deleted SQL Tables Rows. Here, we need to search the Rows’ deletion time.
5 Phase: Get LSN of LOP_BEGIN_XACT Log Record
Now, this is mandatory to use the transaction id to get extract Rows deletion time.
USE RecoverDeletedTables
GO
SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘0000:0000030e’
AND
[Operation] = ‘LOP_BEGIN_XACT’
After that, you can see the information about the Current LSN, Operation, Transaction ID, Transaction Name, etc.
6 Phase: Recover Deleted Tables in SQL Server
Thereafter, you need to change LSN values from hexadecimal to decimal format by adding ‘0x’ before LSN. This is helpful to restore deleted SQL table from database.
–Restoring Full backup with norecovery.
RESTORE DATABASE RecoverDeletedTables_COPY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.bak’
WITH
MOVE ‘RecoverDeletedTables’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.mdf’,
MOVE ‘RecoverDeletedTables_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables.ldf’,
REPLACE, NORECOVERY;
GO
–Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG RecoverDeletedTables_COPY
FROM
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\RecoverDeletedTables_tlogbackup.trn’
ITH
STOPBEFOREMARK = ‘lsn:0x00000015:0000002a:0001’
Now the process to restore deleted table in SQL server has been completed successfully. If you want to see restored deleted SQL table records, then run the below-mentioned command.
USE RecoverDeletedTables_COPY
GO
SELECT * from Employee
Attention: In the above-mentioned code, replace the location ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\ MSSQL\Backup\RecoverDeletedTables.bak’ with the path where your backup file is saved. Please also replace the path of the MDF and LDF files with the path where you have stored the MS SQL database files.
#3) Recover Deleted Table in SQL Server via Automated Tool
If the above-mentioned methods failed to recover deleted table in SQL Server, then you should use SQL Server Recovery Software. This utility has the capability to repair SQL Server Master Database for restoring deleted SQL tables. The tool supports Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000 editions.
How to Recover Deleted Table in SQL Server without Backup?
- Start the software and press Open to insert SQL database file (MDF).
- Now choose advance scan mode to recover deleted objects.
- Thereafter, you can see deleted SQL objects in RED Color and press Export.
- Afterward, your need to choose the Export option accordingly and click on Save.
Benefits of Software
- Restore deleted table in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 R2, 2005, and 2000.
- The tool offers multiple export choices i.e., SQL Server Database, SQL Server Compatible Scripts, and Standard CSV File Format.
- Restore dropped table in SQL server corrupted and damaged database without using any command.
- The software is authorized to repair SQL Server database for restoring all deleted tables and other objects.
Final Thoughts
However, we have discussed most used 3 best ways to recover deleted table in SQL Server. But this is advisable to go with manual steps to restore deleted table in SQL server, if you have SQL Server administrator-level knowledge. If in case, you are a non-technical user then must use the recommended toolkit to resolve your query instantly.
Read Also: Know How to Fix SQL Server Error 18456