Procedure to Restore Table from SQL Backup

Almost all the DBAs have gone through the phase once in their career when they had to restore a single table from SQL Server backup. Whilst it is possible to recover data of a specific table from a SQL backup, this can only be done by restoring the entire backup. Microsoft has not yet introduced any functionality for restoring just a single table without restoring the entire backup, keeping in mind that it might result in a compromise with the referential integrity of the database.

The DBAs do not refer restoring the entire backup because of the below-mentioned reasons:

• It happens that the rest of the data is unharmed and only a specific table has been accidentally damaged.
• Restoring the entire backup will take a considerable amount of time as compared to restoring a single table.

Steps for Restoring Single Table from SQL Backup

The process of restoring only one table from SQL backup contains two most important steps; first restoration of entire SQL backup and second is copying the required table in the destination database.

1. 1. Restoration Of Complete Backup

The first thing that needs to be done is restoration of complete SQL database backup and transaction log backups up to the point prior to deletion of the required table. It is recommended to stop the transaction process before the point when the table was deleted.

This is done with the help of the STOPAT command. Use the below command to restore table from SQL database by using STOPAT parameter.

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

1. 1. Copying Table At Target Database

The next step is to copy the data from the restore backup to the destination database. One of the below-mentioned techniques can be used, depending upon the current scenario:

1. 1. • The table exists but some rows have been deleted from it

If you just want to insert some missing rows in the table, use INSERT statement in combination with SELECT statement.

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


1. 1. • In case the entire table was dropped

In this case, use SELECT INTO statement for copying rows and the table structure in the database.

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

1. 1. • If a few rows were damaged due to any update or some unwanted event

In such a case MERGE statement is used. This statement updates damaged data, inserts data that is missing and deletes the rows that should not exist 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

1. 2. Recreate indexes, constraints or triggers if required.
   3. In case there are any referential integrity issues, resolve them manually.
   4. Run DBCC CHECKTABLE command to verify the data integrity of the table

DBCC CHECKTABLE (“Employee.Details”)

Conclusion

With the help of the procedures mentioned in the above section, the users can easily restore entire table or rows of a table from SQL database backups. One can also use third party SQL backup restore tool to restore a single or multiple tables from .bak file