When you ask a DBA what feature they would most like to see added to the SQL Server recovery functionality, they will undoubtedly say: “The ability to recover a single table from a database backup”.
Of course, it is possible to retrieve the data of a specific table from a database backup, but you cannot do it without restoring the entire backup.
Why do DBA’s want this? Well, the reasons are simple:
- More often than not, the rest of the database is fine, except that someone accidentally damaged or dropped a specific table
- Restoring an entire backup can take a significant amount of time, and generally, when a table has been damaged to such an extent that a restore is required, the DBA will probably be experiencing pressure to get it done fast
If I would have to guess at why Microsoft has not provided this functionality, I would have to say that it probably relates to integrity concerns. If you extract only one table from a backup, which is not at the same point of time as your current database, it might result in referential integrity being compromised. Of course, this is also true if you restore the entire database backup and then just copy out the specified table, so either way, this is something which will always have to be taken into account when performing a single table recovery.
Here is a diagram of the steps which will need to be performed to recover a single table from a backup:
Restore the latest SQL database backup and all log backup files up to the point in time where the data was last known to be correct, as a database with a different name on the same server
This is the part of the process which is likely to take longest to complete, depending on the size of your database
If you only need to extract the data for a couple of tables, it may be faster to use ApexSQL Recover to extract the table data into a new database, instead of having to restore the entire backup. I will show you how this can be done a little later in this article
Copy the data out of the backup into the target database
Depending on the scenario in question, you may use one of the following techniques:
If the table still exists, but only some rows were deleted
Use and INSERT statement combined with a SELECT statement to insert only the missing rows back 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 completely dropped
Use the SELECT INTO statement to copy the rows and the table structure back into the database
USE AdventureWorks2012 GO SELECT * INTO Production.Illustration FROM AdventureWorks2012_Restored.Production.Illustration
If some rows were damaged perhaps through a malicious update or some other unwanted event
The MERGE statement may be used to update damaged or insert data which is missing, or even delete rows which should not 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
- Recreate any indexes, triggers, or constraints if this is required
Resolve any referential integrity issues
This is a manual process and unfortunately there is no way around it. It simply has to be done
Run the DBCC CHECKTABLE command on the table to verify the data integrity
DBCC CHECKTABLE ("Production.Illustration")
As promised earlier, I will now illustrate how you can use ApexSQL Recover to extract that data for the table without doing a full database restore
ApexSQL Recover offers a couple of options for recovering a table. The more information you have about the event that occurred, the easier it will be for you to get the table back using ApexSQL Recover
I.e. if you know that the table was dropped with a DROP TABLE command or deleted using SSMS, you can use the option recover lost data From DROP TABLE operation. This allows ApexSQL Recover to find the data faster, since it only needs to look for dropped tables
Let’s choose that option now
You will be required to connect to the SQL Server database from where the table was dropped
- Provide the connection details
This screen allows you to add any database backups you may have. If you do not have backups available, ApexSQL Recover will only try to recover the data from the database that you are currently connected to
Depending on how long ago the table was dropped, very little information may still be available in the SQL Server database itself. If you do have backups available you should add them here, this will not only speed up the recovery of your data but it will also improve the quality of the data retrieved
Select the Add transaction logs option
- Add all the backups that you have available
If you know the approximate time that the event occurred, choose the relevant option here. Giving an approximate time will speed up the operation
- Select the relevant option
ApexSQL Recover allows you to write the extracted data to a script file or to extract it into a new database
Select your recovery action
Specify the name of the SQL Server database to be created, if applicable
Here you can choose to recover either the structure and the data, or only the table structure
- Select your recovery option
A result screen will be displayed to show what tables were recovered
When something goes wrong and a table gets dropped or accidently truncated, you may not have enough time to restore an entire database. When this happens, ApexSQL Recover is the answer.
ApexSQL Recover can identify the data associated with a single table and extract only the relevant data from your native SQL Server backups, eliminating the need for a time consuming full backup restore.
Please contact us for any problems or questions with the scripts.
January 29, 2014