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