Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

← SQL Server

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!
Thank you!

1,133 votes
Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Gonzalo Bissio shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

104 comments

Sign in
(thinking…)
Sign in with: Microsoft
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Gerald Britton commented  ·   ·  Flag as inappropriate

    Since backups are by page, not object, the only way to do this is restore the entire backup to a new database then copy the tables you want back to the original database

  • Jose C Ortiz commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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!

  • Nate Johnson commented  ·   ·  Flag as inappropriate

    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.

  • Bill commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

← Previous 1 3 4 5 6

SQL Server: Suggestions

Categories

Feedback and Knowledge Base

(thinking…)
Hosted by UserVoice