How to recover one table from a SQL Server info backup

When you raise a DBA what feature they’d most prefer to see additional to the SQL Server recovery practicality, they’re going to doubtless say: “The ability to recover one table from an info backup”.

Of course, it’s potential to retrieve the info of a selected table from an info backup; however you cannot mate while not restoring the complete backup.

Why do DBA’s wish this? Well, the explanations area unit simple:

1. More usually than not, the remainder of the info is ok, except that somebody accidentally broken or born a selected table
2. Restoring a whole backup will take a big quantity of your time, and usually, once a table has been broken to such associate extent that a restore is needed, the DBA can most likely be experiencing pressure to urge it done quick.

If I might need to guess at why Microsoft has not provided this practicality, i might need to say that it most likely relates to integrity considerations. If you extract only 1 table from a backup, that isn’t at a similar purpose of your time as your current info, it’d lead to denotive integrity being compromised. Of course, this can be conjointly true if you restore the complete info backup then simply copy the desired table, therefore either approach, this can be one thing which can continuously need to be taken into consideration once acting one table recovery.

Here could be a diagram of the steps which can ought to be performed to recover one table from a backup:

1. Restore the newest SQL info backup and every one log backup files up to the purpose in time wherever the info was last known to be correct, as a info with a special name on a similar server
   
   This is the a part of the method that is probably going to require longest to complete, reckoning on the scale of your info
   
   If you merely ought to extract the info for a few of tables, it should be quicker to use ApexSQL Recover to extract the table information into a brand new info, rather than having to revive the complete backup. I’ll show you ways this will be done a bit later during this article
   
   
2. Copy the info out of the backup into the target info
   
   Depending on the state of affairs in question, you will use one amongst the subsequent techniques:

     If the table still exists, however just some rows were deleted Use and INSERT statement combined with a choose statement to insert solely the missing rows into the table

USE AdventureWorks2012

GO

SET IDENTITY_INSERT Production.Illustration ON

INSERT INTO Production.Illustration 

(IllustrationID,Diagram,ModifiedDate)

SELECT * FROM AdventureWorks2012_Restored.Production.Illustration

SET IDENTITY_INSERT Production.Illustration OFF

If the table was utterly born

Use the choose INTO statement to repeat the rows and therefore the table structure into the info

 USE AdventureWorks2012

GO

SELECT * INTO Production.Illustration 

FROM AdventureWorks2012_Restored.Production.Illustration

If some rows were broken maybe through a malicious update or another unwanted event

The MERGE statement could also be wont to update broken or insert information that is missing, or maybe delete rows that mustn’t be there

USE AdventureWorks2012

GO

SET IDENTITY_INSERT Production.Illustration ON

MERGE Production.Illustration dest

USING (SELECT * FROM AdventureWorks2012_Restored.Production.Illustration 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.Illustration OFF

1. Recreate any indexes, triggers, or constraints if this can be needed
2. Resolve any denotive integrity problems.
   
   This is a manual method and sadly there’s no approach around it. It merely has got to be done

Run the DBCC CHECKTABLE command on the table to verify the info integrity

DBCC CHECKTABLE ("Production.Illustration")

As secure earlier, I’ll currently illustrate however you’ll be able to use ApexSQL Recover to extract that information for the table while not doing a full info restore

ApexSQL Recover offers a few of choices for sick a table. The additional info you have got regarding the event that occurred, the better it’ll be for you to urge the table back mistreatment ApexSQL Recover

I.e. if you recognize that the table was born with a DROP TABLE command or deleted mistreatment SSMS, you’ll be able to use the choice recover lost information From DROP TABLE operation. this enables ApexSQL Recover to seek out the info quicker, since it solely has to rummage around for born tables

Let’s select that possibility currently

You will be needed to attach to the SQL Server info from wherever the table was born

• Provide the association details
• Click Next

This screen permits you to feature any info backups you will have. If you are doing not have backups accessible, ApexSQL Recover can solely try and recover the info from the info that you just area unit presently connected to

Depending on however way back the table was born, little or no info should be accessible within the SQL Server info itself. If you are doing have backups accessible you must add them here, this can not solely speed up the recovery of your information however it’ll conjointly improve the standard of the info retrieved

5. Select the Add dealing logs possibility

6. Add all the backups that you just have accessible
7. Click Next.

If you recognize the approximate time that the event occurred, select the relevant possibility here. Giving associate approximate time can speed up the operation

8. Select the relevant possibility
9. Click Next

ApexSQL Recover permits you to jot down the extracted information to a script file or to extract it into a brand new info

10. Select your recovery action

11. Specify the name of the SQL Server info to be created, if applicable

12. Click Next

Here you’ll be able to favor to recover either the structure and therefore the information, or solely the table structure

13. Select your recovery possibility
14. Click Recover

A result screen are going to be presented show what tables were recovered

In conclusion:

When one thing goes wrong and a table gets born or accidently truncated, you will not have enough time to revive a whole info. Once this happens, ApexSQL Recover is that the answer.

ApexSQL Recover will determine {the information |the info |the information} related to one table and extract solely the relevant data from your native SQL Server backups, eliminating the requirement for a time intense full backup restore.

If you want to read more about SQl Server database, check this out :