3

I'm wondering how I can restore a single table in SQL Server 2016. For example, I backup the table using SSMS task script option. Delete a row from that table. Now I want to restore the table to bring the deleted row back, How can I do this in SSMS? Thanks!

  • 2
    You backed up the table, or you scripted the table schema, and if the latter, with or without insert statements? You need to add a little more description around "using SSMS task script option"... Generally, you back up a database, and in order to restore a single table from a previous point in time, you restore that database as a different name, apply any logs to get you to the right point in time, and then copy the table. There are 3rd party tools, though, that have features that allow you to extract individual objects from backups without restoring them. – Aaron Bertrand Feb 17 '17 at 20:33
  • Have you actually taken a backup of the table? Can you elaborate on the "script option", did you script out the data as well as the structure? – seventyeightist Feb 17 '17 at 20:33
  • I have a nasty feeling the OP just did "script table as.." – seventyeightist Feb 17 '17 at 20:44
  • Does the table have any "IDENTITY" columns? (auto-increment IDs etc?) – seventyeightist Feb 17 '17 at 21:08
2

If you used the "Generate Scripts" option and included both the schema and the data, you'll have a script containing something like "CREATE TABLE xyz INSERT INTO xyz VALUES ..." with a series of data values to insert. Possibly with a "DROP TABLE" statement at the top, if you included that option when you scripted it.

In that case, execute the generated script (the .sql file) to re-create the table and insert the values to it. Hopefully if there were any indexes etc. you selected the option to include those too. If it's not as simple as dropping and re-creating that table (like if there are foreign key constraints, identity columns etc) you may need to 'restore' the scripted table to a copy of itself, like my_table_new (if the original is my_table) and then re-insert the row from the 'copy' to the original - using ALTER TABLE my_table SET identity_insert on if necessary.

In the future please consider having a proper backup and restore schedule in place, to take care of this kind of thing. Please tell us you aren't running this update in production...!

  • 1
    Hi, Yes, this is exactly what I did. Well i'm banging away on a test server BUT, we got a script to update a user setting en masse in a user table and it was going to eventually run on production. I guess this isn't the recommended approach :-) . I'm sorta green if you haven't noticed, if you had to do something like this, you would use a full backup type of approach? Thanks! – cspell Feb 17 '17 at 21:04
  • So your script includes both the table structure, and the data to insert to it? In that case you just need to DROP the existing table (if the script doesn't already include that) - assuming it's a straightforward overwrite - and execute the .sql script file you saved from the "generate scripts" process. Or if you have dependencies with foreign keys and such like, you could edit the script to create the table as "my_table_new" (if the original was "my_table") and insert the "missing" row from the new table. – seventyeightist Feb 17 '17 at 21:05
  • Yes, when I created the script I used the schema and data options and I see in the generated script that it has the table create and all the inserts for the data. That approach where you import as a new table and just insert the missing rows is interesting..Thanks – cspell Feb 17 '17 at 21:33
0

Without 3rd-party tools, you'll have to do a full restore of the entire database (using a different name, so you don't overwrite the original), then extract the missing data from the copy.

Some 3rd-part backup tools (like Quest Litespeed, for example) have the ability to do an "object-level restore", which can restore just single tables from backup files.

0

we got a script to update a user setting en masse in a user table and it was going to eventually run on production. I guess this isn't the recommended approach :-) .

For something like this it would be much safer to script an update of just the column(s) that need updating in your user table. Cloning any part of your test environment user data into production could be disastrous.

Is it not possible to use the script with which you (presumably) updated your test environment in production as well?

0

IMHO the better way is to full restore the entire DB under a different name and then run the query:

SET IDENTITY_INSERT maindb.mytable ON  -- if there is an auto increment key in the table

INSERT maindb.mytable select * from mybackupdb.mytable where primarykeyfield=value of the row to be restored.

SET IDENTITY_INSERT maindb.mytable OFF

Above SQL is just for example to set you on the right path.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.