Official Blog FREE Viewers for Common Files

How to Restore Only One Table from SQL Server Backup File?

Do you want to restore only one table from SQL Server backup file? Tired of looking for a solution? If yes, then your search is ended here. In this blog, we will help you out to restore single table from SQL Server backup file. Here, we provide a step-by-step solution so that anyone can recover SQL Server Database one table backup easily and without disruption.

Microsoft SQL Server has no inbuilt function to restore a specific table from backup file. Users restore only the entire backup in SQL Server. Microsoft provides a free SQL Server Management Studio software application and T-SQL commands to backup and restore SQL database. Before proceeding to the solution, let’s take a look at the queries of some users to understand their exact requirements.

User Queries- How to Recover Single Table from Backup?

“Does anyone know how to restore only one table from SQL Server backup file? I have a full backup of Microsoft SQL database full backup in the .bak file extension. Now my manager told me to recover a single table from backup file as he needs to check some information. I don’t know if it is possible or not, but need to restore table urgently. Please help me how can I do that? Thanks!”

Alexis Martin, Australia

“With SQL Server 2008, we do a weekly or sometimes monthly full backup because we do not lose the details of our employees and customers. From a week ago, I am looking for a solution to recover a single table from MS SQL Server backup file, but I can not find any clear answer. I want to know if it is possible to restore only one table from backup without restoring the full database backup. Thanks in advance.”

Beckett Smith, USA

How to Restore Only One Table from SQL Server Backup?

There is no direct or instant method to recover specific table from SQL database backup file. First, you need to restore a full database backup to a new database and then copy the required table to live database.

Step 1. Restore Complete Backup

First, you must restore the full backup of the SQL database and the transaction log to the point where the required table was deleted. It is recommended that you stop the transaction process before the time the table was dropped.

You can use STOPAT command to restore a table from SQL database backup.

RESTORE DATABASE Ariadatabase FROM DISK = 'C:\Ariadatabase.BAK'
WITH NORECOVERY
GO
RESTORE LOG Ariadatabase FROM DISK = 'C:\Ariadatabase.TRN'
WITH RECOVERY,
STOPAT = 'May 19, 2016 11:40:00 AM'
GO

Step 2. Copy Required Table to the SQL Database

Now, to restore only one table from SQL Server backup file, you need to copy the data from the backup to the destination database. In the below section, we mentioned the different techniques, you can use one of the following:

Use the INSERT statement combined with a SELECT statement to insert only missing rows in the table.

USE Ariadatabase
GO
SET IDENTITY_INSERT Employee.Details ON
INSERT INTO Employee.Details
(Name, ID, Age)
SELECT * FROM Ariadatabase_Restored.Employee.Details
SET IDENTITY_INSERT Employee.Details OFF

Use the SELECT INTO statement to copy the rows and the table structure back to the original database.

USE Ariadatabase
GO
SELECT * INTO Employee.Details
FROM Ariadatabase_Restored.Employee.Details

You can use the MERGE statement to update damaged data, insert missing data and delete rows that should not be present in the table.

USE Ariadatabase
GO
SET IDENTITY_INSERT Employee.Details ON
MERGE Employee.Details dest
USING (SELECT * FROM Ariadatabase_Restored. Employee.Details src) AS src
ON dest.Name = src.Name
WHEN MATCHED THEN UPDATE
SET dest.ID = src.ID, dest.Age = src.Age
WHEN NOT MATCHED THEN INSERT
(Name, ID, Age) VALUES
(src.Name, src.ID, src.Age);
SET IDENTITY_INSERT Employee.Details OFF

Step 3. Recreate indexes, triggers or constraints if required

Step 4. If any referential integrity issues occur, resolve them manually

Step 5. Run the DBCC CHECKTABLE command on the table to verify the data integrity

DBCC CHECKTABLE (“Employee.Details”)

Suggested Solution to Restore Only One Table From SQL Server Backup

The mentioned method is the perfect solution to recover a specific table from the SQL database backup file. But, above all these things, this process is very time taking and also confusing for non-technical users. If you want the easiest and fastest way to restore only one table from backup SQL, must use SQL Server Backup Recovery software. This is the best application for restoring a single table from the .bak file. It is compatible with all versions of Microsoft SQL Server such as SQL Server 2017, 2016, 2014, 2012, 2008, 2008 R2, etc. The software supports to repair corrupt SQL Server Backup file and restore SQL database from BAK file. You can free download the SQL BAK file Recovery software from the below download link.

The Concluding Words

In this blog, we mentioned the solution to restore only one table from SQL Server backup file step by step. With the help of this method, you can restore single table from SQL Server backup file. If the backup is corrupted, we recommended the best software which helps to repair corrupt BAK file and restore database from backup in SQL Server with complete accuracy.