Assume you have accidentally deleted or dropped a table in Microsoft SQL Server. You lose out on important information, the key to your application. There are ways to recover the lost tables. This post aims to help you recover deleted tables and data from the Microsoft SQL database with ease.
You may have deleted a table accidentally in the Microsoft SQL Server. When deleting or dropping the table, there is a good chance that you have not mentioned where the data should reside. As a result, you lose out on important information.
You can use these methods to recover the lost data. Before we dive into the methods, we would like to take you through the possible reasons for data loss.
Recover Deleted Table in Microsoft SQL Server
Reasons for Data Loss
- It is possible that apart from accidental deletion, you also lose data owing to damaged database files.
- A virus attack or hard disk-related issues can also lead to the deletion of data.
- If you have not backed up the data or forgot to save the data while making changes, you will lose data.
Manual Process
You can restore the deleted table manually by following these steps:
- Use the SQL server management studio for this purpose. You can click on the database to restore the same.
- Use the “from” button to browse the data from the file. Please select the file that you deleted accidentally, and restore it to the database.
- Confirm the restoration process, and your work is done.
However, what if you don’t have a backup file for the data that you deleted? Here, we will discuss the methods that can help you restore the backup for such data.
The Log Sequence Number Method
This is the primary way to deal with the restoration of such data. In case the transaction log backup is missing or cannot be used, you cannot work with this method.
Every transaction with the SQL server is recorded in the logs. You can identify these records with a unique log sequence number. The LSN that works for each part of the data can restore the lost sequences in case of accidental deletion.
To help you understand the same, we will use an example. We will follow all the methods essential for the creation, deletion, and recovery of the table.
Create the Database
Let’s begin by creating the database for this purpose. We will name the database “RecordRecovery.”
What is it that we are recovering in this database? We will have to create another one where we will store employee records. Let’s call this database “employee.”
USE [master];
GO
CREATE DATABASE RecordRecovery;
GO
USE RecoverDeletedRecords;
GO
CREATE TABLE [Employee] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT ‘City1’);
The above code helps create both the databases required as part of this method.
Add Data
Before you can delete the data, you need to add data to the tables. The following code will add three rows to the three columns already present in the table.
USE RecordRecovery;
GO
INSERT INTO Employee DEFAULT VALUES;
GO 100
Delete the Data
It is now important to lose the data to recover it. So, let’s delete some part of the data added into the database to understand the recovery method.
USE RecordRecovery
Go
DELETE Employee
WHERE [Sr.No] < 10
GO
Select * from Employee
With this code, the employees with Sr. No. less than ten will be deleted. You will not find the data in the SQL server.
Find the Deleted Rows
You deleted the rows without defining the move. So, you don’t know where the data is present at the moment. As a result, before you recover the data, you should know where the information is at the moment. The following code will help you find the deleted row. The code will search through the transaction log to recover the data.
USE RecordRecovery
GO
SELECT
[Current LSN],
[Transaction ID],
Operation,
Context,
AllocUnitName
FROM
fn_dblog(NULL, NULL)
WHERE
Operation = ‘LOP_DELETE_ROWS’
Once you have all the deleted rows’ IDs, you can find them and begin recovering the data.
Find the LSN
You need to LOP_BEGIN_XACT log sequence number for database recovery. For this purpose, you will need to use the transaction ID for the same. As a result, you will find the exact time of deleting the records, which is essential for recovering.
USE RecordRecovery
GO
SELECT
[Current LSN],
Operation,
[Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘mentioned in the studio server tool’
AND
[Operation] = ‘LOP_BEGIN_XACT’
Recover the Records
At this point, you have LSN in hexadecimal format. To recover the records, you must convert them into decimal format. You will need to add ‘0X’ as a prefix to the existing LSN to complete recovery.
RESTORE DATABASE RecoverDeletedRecords_COPY
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’
WITH
MOVE ‘RecoverDeletedRecords’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.mdf’,
MOVE ‘RecoverDeletedRecords_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.ldf’,
REPLACE, NORECOVERY;
GO
–Restore Log backup with STOPBEFOREMARK option to recover exact LSN.
RESTORE LOG RecoverDeletedRecords_COPY
FROM
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords_tlogbackup.trn’
WITH
STOPBEFOREMARK = ‘lsn:0x00000014:0000001a:0001’
With this, you have recovered the data. Check to confirm if the data you accidentally lost has also been retrieved as part of this process. In case you have not recovered the lost data, you need to use Stellar Repair for MS SQL, which is your SQL database repair software.
For this purpose, you will need to replace the following
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\Backup\RecoverDeletedRecords.bak’
In your above code with the backup file path. You will also need to replace the .mdf and .idf files with the backup path.
Recovery of SQL Tables When Dropped
It is also possible that you lost the SQL tables due to an accidental drop. Here, we will take you through the different methods to help with the recovery of such tables.
SQL Server Management Studio
This works when you have a complete backup of your database; then, you can recover the dropped table using this method.
It would help if you restored the entire database first.
Now, you will need to generate the script command on SQL server management studio. For this purpose, you can right-click on the database, select the “Tasks” sub-menu, and develop the scripts.
Here, you need to go to the “Objects” tab, select the specific objects that were dropped, and recover them.
You can use the Scripting Options tab and select “single file” or “single file per object” to generate the options. At this point, you need to specify the path.
In the advanced tab, you can select if you want to generate the scripts for dependent objects and the type of scripts you wish to recover, i.e., schema or data.
You can open or modify the script once it’s generated. These scripts create the dropped tables before they add the lost data.
To know if you have recovered all the lost data, you need to run the recovered script against the original one.
ApexSQL Diff
This is another method of recovering dropped tables from the MySQL server. You can also use this method to recover tables that have not been dropped but lost accidentally.
How does it work? It is a synchronization and comparison tool for SQL server databases. You can quickly identify the differences between the database objects, create comprehensive reports, and synchronize the database using this tool.
- In the ApexSQL Diff tool, go to the project management window to create a new project.
- There is a source side to the data source tab. Here, select the backup button from the drop-down list.
- Click the Add button. You can add the full database backup alone or along with the chain of differential database backup.
- Go to the destination side of the tool for the next couple of steps. Go to the database in the drop-down list. Now, you will need to select the SQL instance for which you plan the recovery. Once you have made the selection, look for the authentication method that works for you within the SQL server.
- Mention the production database name in the database drop-down.
Next, you will need to make the necessary selections in the Advanced tab. In the Advanced tab, you will see four more options:
- Select Objects filter. Now, go to the Tables node in this space.
- In the Tables list, select the dropped table.
- Now, click Compare and check the results in the Results Grid. You will see the dropped table as part of the result. Now, select the table you want to recover.
- Synchronize the home tab to generate the script for recovery of the dropped table.
Conclusion
You can also use SQL recovery software tools that are available to recover the database table. Whatever method you use, you need first to locate the dropped tables before fixing the accidental deletion. Knowing these handy tips on SQL servers and recovery of databases can help you save a lot of time.
