Reverting accidental delete/update without recent backup
Issue: At times, we may run/execute an update/delete script accidentally and then start googling to find if there is a way to revert it.
Requirement to fix the issue: Old Backup of the same Database.
Solution:
The solution given below is helpful if you have some old Database backup.
Steps:
Note the approximate time at which you executed the update/delete script. Restoring the DB to a minute earlier to that time would make sure that we haven’t lost any updates on the DB.
Take a ‘Transaction Log’ backup of the Database to be restored by selecting the ‘Transaction Log’ from the ‘Backup Type’ dropdown. To get the "Transaction Log" in the dropdown, make sure the DB Recover Model is "FULL".
To make a DB recover model as "Full":
After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, and then click Properties, which opens the Database Properties dialog box.
In the Select a page pane, click Options.
The current recovery model is displayed in the Recovery model list box.
Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.
Click OK.
Now ‘Restore’ the old backup of the database you have. Make sure that select the ‘RESTORE WITH NORECOVERY’ option in the ‘Recovery State’ dropdown in Restore options.
Once the Old backup is restored, now restore the Transaction Log Backup specifying the timeline which is BEFORE the update/delete script execution.Also make sure to select the ‘RESTORE WITH RECOVERY’ option in Restore Options.
Now run a SELECT script and check the records. They would contain the values before the Update/Delete script execution.
