{"id":89878,"date":"2021-11-16T09:03:57","date_gmt":"2021-11-16T09:03:57","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=89878"},"modified":"2024-03-11T11:32:32","modified_gmt":"2024-03-11T11:32:32","slug":"restore-specific-table-from-sql-database-backup-file","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/restore-specific-table-from-sql-database-backup-file\/","title":{"rendered":"How to Restore Specific Table(s) from SQL Database Backup File?"},"content":{"rendered":"

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<\/a> 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.<\/p>

How to Recover Specific Table(s) from SQL Database Backup File?<\/strong><\/h2>

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.<\/p>

Scenario 1: When rows are deleted and tables still exist<\/strong><\/h3>

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:<\/p>

USE My_Database
GO
SET IDENTITY_INSERT my_table ON
INSERT INTO my_table_1 (column_name)<\/code><\/pre>
SELECT * FROM My_Database_Restored.my_table
SET IDENTITY_INSERT my_table OFF<\/code><\/pre>
Scenario 2: When the table was completely dropped and needs to be completely recreated<\/strong><\/h3>
You can use the following code with the SELECT INTO command for copying the rows and table structure to the original database.<\/p>
USE My_Database
GO
SELECT * INTO my_table
FROM My_Database_Restored.my_table<\/code><\/pre>
Scenario 3: When the table has a few damaged rows due to an update or an unwanted event<\/strong><\/h3>
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.<\/p>
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<\/code><\/pre>
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:<\/p>
DBCC CHECKTABLE (“my_table”)<\/code><\/pre>
Drawback of Using the Manual Method<\/strong><\/h2>
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.<\/p>
An Alternative Solution<\/strong><\/h2>
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 Repair for MS SQL Technician<\/a>, which can help you restore specific tables from .BAK file.<\/p>
\n
\"free<\/a><\/figure>\n<\/div>
How to Use Stellar Backup Extractor for MS SQL?<\/strong><\/h3>
Let’s explore the steps to recover tables from SQL database backup file by using the software:<\/p>