Restore a table from a backup
Hi Msft team!
I'm writing to reccomending a new feature that will help us a lot! Every time that we have data issues (truncate or DML by accident) we need to restore the entire database or filegroup and that takes a lot of time! it will be awesome if you can add the posibility to restore a table from a backup or restore to a point in time directly from SSMS instead off the whole process.
I hope this helps!
This is something that we’ve had on our radar for some time, but haven’t been able to get it prioritized. Will definitely consider for future releases.
Redgate can't read compressed backup's
Wim Buyens commented
I think with the Redgate Toolbelt u can do this. Because u can also compare from a database with a backup. Redgate has really great tools.. I would look there https://www.red-gate.com/products/
Jose C Ortiz commented
is there any update on this? we've been in pain because this feature is not on SQL server. sometimes having to restore databases with TB of data to only restore few rows-
Andy Steinke commented
I feel like the System 36/38/AS400 has had this for about as long as I've been alive (if we include the skunkworks time developing S/36)...Not every database is a wonderfully elegant overengineered with perfect integrity masterpiece.
Third party options in my past have been far less than performant, often requiring scanning a full backup and the scanning it again to retrieve the table. Is this really something that needs to be left to partners or can it be considered somewhat core functionality? In the real world, "restore full database" is not the base unit of restore. Thanks!
This concept already exists in LiteSpeed called object restore
Rezwan M commented
Yes, we need it ,MS team should address this.
Eddie Panther commented
Too many restores to only get the data from one table. Long overdue.
This would be a great feature and would bring some ease in DBA lives
Ajay Dwivedi commented
Nate Johnson commented
Upvoting. Continues to be a thorn in our side, albeit a generally infrequent one. To address Bill's comments below, and some of the general push-back. You DON'T typically restore the table "OVER ITSELF". You restore it as a copy, like "MyTable_bak", and you query it and fetch the data you need to restore. And sure, sometimes you truncate & reload the existing "real/live" table, but you as the DBA *know* what you're doing and the side-effects that could have (triggers & constraints etc.). So stop making excuses, MS. This isn't that difficult. Your backup structures just need to change to support it. If all the 3rd party vendors can do it, so can you.
There are challenges/problems with restoring a table. RI gets blown away. Triggers are't undone. More generally, changes to other tables (that are based upon changes to the table that is being restored) will remain in place (whether the modifications were done in the same transaction that modified the table that is being restored, or as commonly done in a different transaction at some later time). SQL Server 6.5 allowed table restores. It was a slick way to replace a corrupted table with a clean one. But one consequence was that table restores created all sorts of **** with applications. Perhaps allow it when a ALLOW_LOGICAL_INCONSISTENCIES clause is passed. Of course, that clause will cause customer's to say "But, I don't wan't logical inconsistencies!" Caveat emptor!
Jeff Moden commented
In a Development or Testing environment, it can happen a lot, Pete. But even if that's not important to some (and it is important), it only takes once on a 24/7 large database to have a real appreciation for such a feature.
Completely agree, thank you Chrissy for addressing this issue.
Chrissy LeMaire commented
Amadou Barry commented
This will be very good feature to have.
Robin Wilson commented
This would be so helpful. Better than having to restore the entire multi-GB database elsewhere and then have to write a script to do this when the table in question may be tiny.
Would need consideration on how to handle issues with database constraints though.
Blaise Gachet commented
Should be part of a backup/restore handling.