Sql-server – How to restore single table in Sql Server 2016

sql serversql-server-2016

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!

Best Answer

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...!

Related Question