How to Recover Deleted Table From SQL Server Database?
Summary: In this write-up, we will discuss some simple and effective methods that you can use to recover deleted table from SQL Server. So, keep reading this post because you are very close to know the solution.
MS SQL Server is an extremely admired relational database management system among business & corporate sectors. It stores all its data in MDF, NDF, and LDF database files. Sometimes, SQL database tables are deleted by users during the management and maintenance of the databases, due to which administrators face many serious issues. Hence, it is important to recover deleted data from SQL server to get back to the normal workflow.
So if you are also such a user who needs quick and reliable solution for how to recover deleted table from SQL Server then this platform is the right place to overcome this circumstance. Here, you will get the manual as well as an automated solution for retrieving deleted table from the SQL Server database. Before jumping onto the solution part it is necessary to know the reasons for data loss in SQL Server. This way users can execute SQL server recover dropped table in a better way.
Possible Reasons Behind SQL Server Table Data Loss
- Damaged Database Files (.mdf/.ndf) – Data loss is often connected with file corruption. 60% of users lose their data files due to damaged files in their databases.
- Problem with the Hard Disk – Hardware components like hard risk can also go false with issues like bad sector, etc. Therefore, users face data loss in SQL tables.
- Sudden Power Failure – Abrupt power outages & improper shutdowns are equally responsible for such data loss issues. Moreover, this can cause even bigger problems for users.
- Corrupted File System – Instead of files, when the entire file system faces corruption, data loss is quite obvious in that case.
- Human Error – There are scenarios where users delete important files by themselves. Sometimes it is on purpose & in some cases, it’s by accident.
- Virus Attack – Virus attacks like malware, SQL injection, ransomware, etc are some cases where users need to be alert as they often target data.
MS SQL Server Table Restore – Common Commands to Know
It is a well-known fact that the SQL Server database stores data files in the form of tables. This even includes records & all other data as well. This system is so robust that it’s still in high demand almost after 49 years of introduction. Mainly to satisfy the data management needs.
For example, businesses use this SQL database for storing the details of their customers, staff, inventory, etc. An employee database includes fields like Employee ID, Employee Name, Address, Age & Sex, Phone Numbers, Email Id, etc.
Well, there are plenty of commands for various functions. Also, the commands for learning how to recover deleted table in SQL Server are quite complex. However, the most common ones for a table are:
- CREATE: Creates a new SQL table or object in the database.
- INSERT: Insert records in table for data storing & management.
- UPDATE: It modifies or updates records in SQL table if needed.
- DELETE: It deletes a single or selective record based on needs.
- DROP: Delete an entire table including all of the available records.
- GRANT: Assigns the privileges & permissions to users working on it.
- REVOKE: This is used to get back the assigned privileges & permissions.
#1 Restore Deleted Data from Backup Using SSMS
Follow the below-mentioned instructions to recover deleted table from SQL Server using the backup file:
Step-1. Launch SQL Server Management Studio (SSMS) on your machine. Right-click on the databases folder and select the Restore Database option.
Step-2. Afterward, check the radio button corresponding to From Device and then hit the browse button (…).
Step-3. Here, select backup media type as File and click on the Add button.
Select the backup file to restore the database and press the OK button.
Step-4. Now, your database is restored successfully, a confirmation message will pop up simply hit the OK button.
#2 How to Retrieve Deleted Table in SQL Using LSN Method
Now, as we know that the Log Sequence Number or LSN method can’t help in restoring the deleted data. It only restores the damaged & missing transaction log data. In such cases, this technique is quite useful for learning how to recover deleted table in SQL Server DB.
There are a total of six steps in this technique, so let’s explore them one after another with their respective commands.
Step-1. Create A Database First
First of all, all that users need to do is just create a database. For example, here we are creating a database named TestCompany & tabel named “Employees” to proceed.
USE [master];
GO
CREATE DATABASE TestCompany;
GO
USE TestCompany;
GO
CREATE TABLE [Employees] ()
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT ‘City1’);
Step-2. Insert Data in Newly Created SQL Table
As we created the table in SQL database in the above step, it’s time to add data in the columns we created. Follow the below command for that:
USE TestCompany;
GO
INSERT INTO Employees DEFAULT VALUES;
GO 100
Step-3. Delete Rows in Your SQL Table
USE TestCompany
Go
DELETE Employees
WHERE [Sr.No] < 10
GO
Select * from Employee
Step-4. Check Deleted SQL Table Info
Now, users need to search for the log data files for acquiring information of the deleted table. The command for the same is:
USE TestCompany
GO
SELECT
[Current LSN],
[Transaction ID],
Operation,
Context,
AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = ‘LOP_DELETE_ROWS’
Now, we have got the transaction IDs of the rows deleted in SQL Server table. It’s time for users to find out the time when the rows were deleted.
Step-5. Know LSN of LOP_BEGIN_XACT Log Record
The transaction ID is mandatory here for users to proceed & find the deletion time. Follow this command to get the desired results:
USE TestCompany
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’
It shows the Current LSN, Operation, Transaction ID, Transaction Name, etc details to users.
Step-6. How to Recover Deleted Table in SQL Server
Afterward, users need to change the LSN values from hexadecimal to decimal format. This should be done by prefixing each LSN with ‘0x.’ This is the most crucial part for users to recover deleted table in SQL Server safely.
–Restoring Full backup with norecovery.
RESTORE DATABASE TestCompany_COPY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.bak’
WITH
MOVE ‘TestCompany’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.mdf’,
MOVE ‘TestCompany_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany.ldf’,
REPLACE, NORECOVERY;
GO
–Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG TestCompany_COPY
FROM
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.BITRECOVER\MSSQL\Backup\TestCompany_tlogbackup.trn’
ITH
STOPBEFOREMARK = ‘lsn:0x00000015:0000002a:0001’
To view the restored database records, users must go through this command:
USE TestCompany_COPY
GO
SELECT * from Employee
#3 Professional Way to Recover Deleted Table From SQL Server
The above-mentioned manual method does not work if you don’t have a healthy backup of your database. In such a situation, you can take the help of an Enterprise-Grade Level professional SQL Database Recovery tool. This software has the potential to recover deleted data from SQL Server without losing a single bit of data. By using this software, the user can also recover the permanently deleted data including views, triggers, functions, rules, etc. in just a few simple clicks and restore SQL database with different name. This tool shows the preview of the deleted database items in red color so that it can be easily recognizable by the user.
Also, by using this utility users can easily recover the database items from damaged or corrupt SQL database files and restore SQL database primary file. The graphical user interface of this tool is simple and easy to use so that a technical and non-technical user can easily use it. In a nutshell we can say that, for SQL server recover dropped table operation, it’s an ideal choice.
Steps to Recover Deleted Table From SQL Server
Step-1. Download the Software and install it in your Windows OS system to learn how to recover deleted table in SQL Server.
Step-2. click on the Open button to load the SQL database file (.mdf file).
Step-3. Select a scan mode and then choose the Server version of MDF file either manually or automatically. Also check the box corresponding to the Recover Deleted Objects option to recover deleted database objects from SQL Server and click the OK button.
Step-4. Once the scanning process gets completed, preview deleted database items and press the Export button to export the recovered database.
Step-5. Afterward, select an export option (SQL Server Database or SQL Server Compatible Scripts or CSL File Format) and fill all the export details accordingly. Finally, hit click the Export button for SQL server recover dropped table.
Also Read: How to Recover Deleted Objects in MS SQL Server Without Hassles
How to Retrieve Deleted Table in SQL & What Features Do We Have?
- Ability to restore deleted tables in SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 / R2, 2005, and 2000.
- There are several export options present for users i.e., SQL Server Live Database, SQL Server Compatible Transcript Files, and Standard CSV File Format.
- Easily recover the dropped table in SQL server having corrupted table data without using any complex commands.
- This software is enabled with advanced algorithms that can help users counter all kinds of errors.
- Also, this advanced solution is even capable of recovering ransomware-affected database files in a safe & easy manner.
Final Words
Well, there are times when SQL Server users face disastrous situations in their databases. The scenarios could take place when the user accidentally deleted tables from the SQL Server database. Therefore, users must be aware of the methods to recover deleted table from SQL Server in case of any misadventure.
In this post we have mentioned both the manual as well as an automated solution that you can use to recover deleted data from SQL Server. Just go through the blog and choose whatever option works best for you.
