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.
How to Recover Specific Table(s) 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”)
Drawback of Using the Manual Method
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.
An Alternative Solution
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, 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:
- Download and launch the Stellar Repair for MS SQL Technician and select the Stellar Backup Extractor for MS SQL module.
- A dialog box will open. You can use the ‘Browse’ open to select the file (if you know the location) or use the ‘Search’ option to locate the .bak file in your system.
- Choose the file from which you want to extract data and click Scan.
- After clicking the Scan, select any of the given modes:
- Standard Scan (Recommended) – This mode scans and repairs normal corruption in .BAK file.
- Advanced Scan – Use this mode when you do not get desired result after Standard scan. This mode scans the severely corrupted .BAK file.
- Now, you will get a new dialog box, ‘Select Backup Set’. This will display the backups if they are stored in different files.
- Select the Backup Set and click ‘Next’.
- When the repair is completed, you will get a ‘Repair Complete’ dialog box.
- You can see the preview of the repaired files.
- Now, you can select the tables you want to save and then click ‘Save’.
- A new dialog box comes up, displaying multiple saving options – MDF, CSV, HTML, and XLS.
- Select the saving option as MDF and enter the required details.
- Choose the destination for saving the file and click ‘Save’.
- The ‘Save Complete’ dialog box appears when the data is successfully saved.
Conclusion
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 Repair for MS SQL Technician , to selectively restore the tables from backup (.BAK) file.
