Share via

How to Recover a Single Table from a SQL Server

  • Article

Introduction:

When you raise a DBA, what feature they'd most prefer to see added to the SQL Server recovery practicality, they'll beyond any doubt say: "The ability to recover one table from an info backup." Of course, it's attainable to retrieve the info of a particular table from an info backup; however, you cannot make love while not restoring the complete backup. Why do DBAs with this? 

Well, the explanations square measure simple: More usually than not, the remainder of the information is okay, except that somebody has accidentally broken or born a particular table. Restoring a complete backup will take a large quantity of your time. Once a table has been broken to such an associate extent that a restore is needed, the DBA can most likely experience pressure to induce it done quickly. If I might need to guess why Microsoft has not provided this practicality, I might need to say that it most likely relates to integrity issues. If you extract only 1 table from a backup that isn't at the constant purpose of your time as your current info, it will lead to denotative integrity being compromised. Of course, this can be conjointly true if you restore the complete info backup and then copy the desired table, thus either means. It can be one thing that can continuously need to be considered once playing one table recovery.

Illustration diagram of how it works:

Procedure to recover a single table:

  • Restore the newest SQL info backup, and every one log backup files up to the purpose whenever the info was last far-famed to be correct, as info with a particular name on an equivalent server.

It is a part of the method that will probably require the longest to complete, looking at yours's dimensions. 

 

  • Copy the info out of the backup into the target info. Depending on the situation in question, you'll use one in each of the subsequent techniques: 
    • If few rows got deleted, and the table still exists

Use an INSERT statement combined with a pick statement to insert the missing rows solely into the table.

USE dummy  table
GO
SET IDENTITY_INSERT Production.TEMP ON
INSERT INTO  Production.TEMP
(IllustrationID, Diagram, ModifiedDate)
SELECT * FROM dummyTable_Restored.Production.TEMP
SET IDENTITY_INSERT Production.TEMP OFF
  • If the table was completely dropped

Use the SELECT INTO statement to copy the rows and the table structure back into the database

USE dummy  table
GO
SELECT * INTO Production.TEMP
FROM dummyTable_Restored.Production.TEMP
  • If some rows were broken maybe through a malicious update or another unwanted event The MERGE statement is also wont to update broken or insert information that is missing, or maybe delete rows that mustn't be there
USE dummyTable
GO
SET IDENTITY_INSERT Production.TEMP ON
MERGE Production.TEMP dest
USING (SELECT * FROM dummyTable_Restored.Production.TEMP src) AS src
 ON dest.IllustrationID = src.IllustrationID
WHEN MATCHED THEN UPDATE
SET dest.Diagram = src.Diagram, dest.ModifiedDate = src.ModifiedDate
WHEN NOT MATCHED  THEN  INSERT
(IllustrationID,Diagram,ModifiedDate) VALUES
(src.IllustrationID,src.Diagram,src.ModifiedDate);
SET IDENTITY_INSERT Production.TEMP OFF
  1. Recreate any indexes, triggers, or constraints if this is required
  2. Resolve any referential integrity issues.
    This is a manual process and unfortunately, there is no way around it. It simply has to be done
  3. Run the DBCC CHECKTABLE command on the table to verify the data integrity
DBCC CHECKTABLE ("Production.TEMP ")

Conclusion:

As we know that Database management is essential to run any business. For example, you have software for your personal restaurant and all of the records like transactions and items are stored in a database. When something goes wrong and a table gets dropped or accidentally truncate. You can face a huge loss after losing your data. Because you have very important data in tables like transactions etc.; then, you can recover the single table data by manual solutions that I have mentioned above in the article. Definitely, this takes a long because this procedure will recover the whole database and then will recover your specified table, and, in this situation, you have no time to think about something else. To resolve this issue as soon as possible, you can restore the database from updated backup copy or take the help from Microsoft support team.