Prerequisites before Restoring a Specific SQL Table<\/h2>
Before restoring specific tables from SQL database backup, confirm the following:<\/p>
- \n
- Make sure you’ve access to the SQL Server instance<\/li>\n\n\n\n
- Ensure your system have free space available, i.e. equal to the original data size.<\/li>\n\n\n\n
- You have all permissions on your database, like RESTORE DATABASE privileges.<\/li>\n\n\n\n
- There should be a healthy backup file and you can verify it.<\/li>\n\n\n\n
- Make sure the name of the database you create using your backups is distinct. This helps to avoid conflicts.<\/li>\n<\/ul>
How to Recover Specific Table(s) from SQL Database Backup File?<\/strong><\/h2>
To restore individual table from the backup file, follow these methods.<\/p>
Method 1 – Restore your Backup using SSMS<\/h3>
To recover a specific table in SQL Server, here are the below-cited steps:<\/p>
Step 1: Restore the Backup as a Separate Database<\/h4>
The first step is to restore the backup to a new database with a different name.<\/p>
For example:<\/p>
Original Database:<\/code><\/pre>StellarDB<\/code><\/pre>Restored Database:<\/code><\/pre>StellarDB_Restored<\/code><\/pre>The restored database acts as a recovery source and allows you to access the tables stored inside the backup. Next, you can use the restored database as a recovery source to allow you access the tables in the backup file. You can also check the logical file names stored in the backup.<\/p>
The below command will display the logical file names in backup.<\/p>
RESTORE FILELISTONLY<\/code><\/pre>FROM DISK = 'D:\\BackupSQL\\StellarDB.bak'<\/code><\/pre>![\"check](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/DBMS-window-1-1024x424.png\")
<\/figure>Next, restore the backup by using this command:<\/p>
RESTORE DATABASE StellarDB_Restored<\/code><\/pre>FROM DISK = N'C:\\SQLBackups\\StellarDB.bak'<\/code><\/pre>WITH<\/code><\/pre>MOVE 'StellarDB' TO 'C:\\SQLData\\StellarDB_Restored.mdf',<\/code><\/pre>MOVE 'StellarDB_log' TO 'C:\\SQLData\\StellarDB_Restored_log.ldf',<\/code><\/pre>STATS = 10;<\/code><\/pre>GO<\/code><\/pre>\n
Note: <\/strong>The MOVE option tells SQL Server to create new physical database files for the restored database. Without this option, your restore operation may fail due to file name conflicts.<\/p>\n<\/blockquote>
After the restore operation completes successfully, SQL Server contains two separate databases:<\/p>
- \n
- Original database<\/li>\n\n\n\n
- The restored database containing database copy<\/li>\n<\/ul>
Before recovering, confirm that the table is available in the restored database:<\/p>
SELECT *<\/code><\/pre>FROM StellarDB_Restored.dbo.EMP;<\/code><\/pre>GO<\/code><\/pre>If it returns the records, the table is ready for recovery.<\/p>
Step 2: Recover the Required Table<\/h4>
After restoring the database backup to a new database, you can copy the specific tables to the destination database. Below, we’ve mentioned the process to restore specific tables based on three scenarios.<\/p>
Scenario 1: When rows are deleted and tables still exist<\/strong><\/p>
You can use the following code in SSMS wherein you can combine the INSERT command with the SELECT command (see the below example). 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: SQL database table was dropped completely and you need to recreate them:<\/strong><\/p>
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: Due to Updates or an unexpected event, some rows in your database table has damaged<\/strong><\/p>
You can run the below command to update the damaged and missing data:<\/p>
USE My_Database\nGO\nSET IDENTITY_INSERT my_table ON\nMERGE my_table dest\nUSING (SELECT * FROM My_Database_Restored.dbo.my_table) AS src \nON dest.(column_name_1)= src.(column_name_1)\nWHEN MATCHED THEN UPDATE\nSET dest. (column_name_2) = src. (column_name_2), dest. (column_name_3) = src. (column_name_3)\nWHEN NOT MATCHED THEN INSERT\n(column_name_1,column_name_2,column_name_3) VALUES\n(src. (column_name_1), src. (column_name_2), src. (column_name_3));\nSET IDENTITY_INSERT my_table OFF<\/code><\/pre>You may have to recreate indexes, views, constraints, triggers, or rules functions, if table contains them.<\/p>
Next, check SQL database for integrity issues, using the DBCC CHECKDB command<\/a>.<\/p>
Drawbacks of Recovering Specific Tables using the Manual Method<\/strong><\/p>
Some common limitations are:<\/p>
- \n
- You need to restore the entire database backup, even if only one table needs to be recovered.<\/li>\n\n\n\n
- The large backup restore can take time and also require temporary storage space.<\/li>\n\n\n\n
- The native tools may not help you recover specific objects, if the backup file is corrupted.<\/li>\n\n\n\n
- SQL Server version compatibility issues may prevent the backup from being restored successfully.<\/li>\n\n\n\n
- After recovery, it may require additional validation table dependencies such as foreign keys, triggers, indexes, and constraints.<\/li>\n\n\n\n
- SQL Server does not provide a preview of table contents before completing the restore operation.<\/li>\n<\/ul>
Method 2: Restore Specific Tables using Stellar Backup Extractor for MS SQL – An Alternative Solution<\/h3>
If the above method fails, your backup file gets corrupt, or you may not be able to restore any of your table(s), then you can use Stellar Backup Extractor for MS SQL module in Stellar Repair for MS SQL Technician<\/a>. This tool can help you restore specific tables from .BAK file. It doesn’t require a full SQL Server database restore. It even works if your backup file is corrupted.<\/p>
\n![\"Windows](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/Windows-Download-button.png\")
<\/a><\/figure>\n<\/div><\/a>How to Use Stellar Backup Extractor for MS SQL?<\/strong><\/h2>
Let’s explore the steps to recover tables from SQL database backup file by using the software:<\/p>
- \n
- Download and launch the Stellar Repair for MS SQL Technician <\/strong>and select the Stellar Backup Extractor for MS SQL<\/strong> module.<\/li>\n\n\n\n
- A dialog box will open. You can use the ‘Browse<\/strong>’ open to select the file (if you know the location) or use the ‘Search’<\/strong> option to locate the .bak file on your system.<\/li>\n<\/ul>
![\"select](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/Seclect-file.png\")
<\/figure>- \n
- Choose the file from which you want to extract data and click Scan.<\/strong><\/li>\n<\/ul>
- A dialog box will open. You can use the ‘Browse<\/strong>’ open to select the file (if you know the location) or use the ‘Search’<\/strong> option to locate the .bak file on your system.<\/li>\n<\/ul>
- Download and launch the Stellar Repair for MS SQL Technician <\/strong>and select the Stellar Backup Extractor for MS SQL<\/strong> module.<\/li>\n\n\n\n
![\"Choose](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/choose-extract-file.png\")
<\/figure>![\"clicking](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/Select-file-mode.png\")
<\/figure>![\"Select](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/Select-backup-set.png\")
<\/figure>![\"Preview](\"https:\/\/www.stellarinfo.com\/blog\/wp-content\/uploads\/2021\/11\/Repared-preview.png\")
<\/figure>