In this article I am going to tell you how you can restore a single table from SQL Backup. I know many of you must be familiar with the technique of restoring the whole data from the SQL Backup. But restoring a particular table from backups is a difficult task.
Many users have a doubt that if we can restore the whole data from SQL Backup then what is the need for restoring a single table separately. So I would like to clear their doubts that if you restore whole data then it will occupy a lot of space and it will be difficult for you to find the needed file in the bulk files.
Therefore there are some manual ways through which you can restore the table from SQL Backup according to your need.
Steps to Restore Table from SQL Backup Files
- Restore Data from Backup– First of all start the restoration process and stop at the point where you have deleted the table. You must stop the just prior to the deletion of the needed table.
Use STOPAT Command to restore a table from the SQL Backup.
“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”
- Restore the Deleted Table
There are three situations for this and three similarly three different commands.
- Some Rows might be found Missing– You can restore the deleted rows by using a combination if INSERT and 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”
- The whole Table is Deleted – If the whole table is deleted then you can use SELECT INTO Statement to get backs rows and table from the database.
“USE Ariadatabase
GO
SELECT * INTO Employee.Details
FROM Ariadatabase_Restored.Employee.Details”
- Partial Damages to Rows due to Updates – If few rows are damaged then you can use the MERGE Statement. This statement deletes the unwanted rows and adds the missing data.
“USE Ariadatabase
GO
SET IDENTITY_INSERT Employee.Details ON
MERGE Employee.Detailsdest
USING (SELECT * FROM Ariadatabase_Restored. Employee.Detailssrc) 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”
- Make New indexes, Constraints and Triggers
- Manually Resolve the Referential Integrity Issues if you found any
- Verify the Data Integrity by running the DBCC CHECKTABLE Command
“DBCC CHECKTABLE (“Employee.Details”)”
I hope by now you must have restored your table from SQL backups. But I know the fact that many of the non-technical users find it difficult as moving a mountain.
Well this is true that the manual techniques will take a lot of time without any kind of assurance of recovery. So what should you do now?
I must suggest that you must go for SQL Backup Recovery Software if you don’t want to do this much complex stuff. The reason for that is not only it will save your time but also gives the 100% guarantee for restoring the tables from the SQL Backup files.
You must only go for this tool if you fulfill the following Criteria:-
- Must have minimumPentium or above processor
- 512 MB RAM is needed
- 50 MB of Minimum Space
- Sufficient Space to save the recovered files
Conclusion
The ball is in your court and you have to decide whether to follow the complex methods or go for the easy and assured tool. I have already mentioned both the ways, so ultimately it is your call to make. I hope the information I have provided is enough for you to restore a table from the SQL Server.
The post How to Restore Table From SQL Backup Files??? appeared first on SysInfoTools Software.
from SysInfoTools Software http://bit.ly/2Sw0xce