Summary: In this post, we have mentioned the ways to restore specific tables from a SQL database backup file. In addition, we have mentioned an advanced SQL backup recovery tool that can help in restoring specific tables from .BAK file, even if it is corrupted.
Contents
MS SQL does not provide any in-built functionality to directly restore specific tables from the backup file. However, you can restore the entire SQL backup by using SQL Server Management Studio (SSMS) or T-SQL commands. After that, you can copy the specific tables to your database. In this post, we’ll be discussing the process to restore specific tables from SQL database backup file.
After restoring the database backup to a new database, you can copy the specific tables to destination database. Below, we’ve mentioned process to restore specific tables based on three scenarios.
Scenario 1: When rows are deleted and tables still exist
You can use the following code in SSMS wherein you can combine the INSERT command with the SELECT command. This will only add the missing rows to the table in the original database:
USE My_Database
GO
SET IDENTITY_INSERT my_table ON
INSERT INTO my_table_1 (column_name)
SELECT * FROM My_Database_Restored.my_table
SET IDENTITY_INSERT my_table OFF
Scenario 2: When the table was completely dropped and needs to be completely recreated
You can use the following code with the SELECT INTO command for copying the rows and table structure to the original database.
USE My_Database
GO
SELECT * INTO my_table
FROM My_Database_Restored.my_table
Scenario 3: When the table has a few damaged rows due to an update or an unwanted event
You can use the following code with the MERGE command for updating the damaged data, inserting the missing data, and deleting the unnecessary rows in the table.
USE My_Database
GO
SET IDENTITY_INSERT my_table ON
MERGE my_table dest
USING (SELECT * FROM My_Database_Restored. my_table src) AS src
ON dest.(column_name_1)= src.(column_name_1)
WHEN MATCHED THEN UPDATE
SET dest. (column_name_2) = src. (column_name_2), dest. (column_name_3) = src. (column_name_3)
WHEN NOT MATCHED THEN INSERT
(column_name_1,column_name_2,column_name_3) VALUES
(src. (column_name_1), src. (column_name_2), src. (column_name_3));
SET IDENTITY_INSERT my_table OFF
If there are any indexes, views, constraints, triggers, or rules functions in your table, you may have to recreate them if needed. Along with this, you may have to address the referential integrity issues manually. To check the data integrity, use the following command:
DBCC CHECKTABLE (“my_table”)
In the above technique, you need to restore the entire database backup to retrieve only one or some specific tables. This procedure can take a long time and require a lot of free space on the SQL Server. Also, if the backup file is corrupt, you may not be able to retrieve the data.
If your backup file gets corrupt, you may not be able to restore any of your table(s). In such a case, you can use Stellar Backup Extractor for MS SQL module from Stellar Toolkit for MS SQL, which can help you restore specific tables from .BAK file.
Let’s explore the steps to recover tables from SQL database backup file by using the software:
In this post, we have outlined the solutions to restore specific tables from SQL Server backup file. You can restore the tables from a SQL Server backup file using the manual method mentioned in this post. However, the manual method is time-consuming, complex, and may not bring the desired results. Alternatively, you can use Stellar Backup Extractor for MS SQL, which is a part of Stellar Toolkit for MS SQL, to selectively restore the tables from backup (.BAK) file.
Saba Zehra Rizvi is Content Team Lead at Stellar. She specializes in providing solutions for issues related to Outlook, Excel, Access, and SQL. She also has a knack for writing process-based blogs, articles, and knowledge base on file conversion tools, such as GroupWise, OLM, NSF, MBOX and others. She also guides her team on strategizing the content and time management
Stellar Data Recovery has the right Windows Recovery tool for all your data recovery
Read More
Stellar Data Recovery for Mac program performs safe..
Read More
A comprehensive photo recovery software to restore photos, music & video files
Read More
Powerful video repair tool for repairing corrupt or damaged MOV and other video files
Read More
