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:
1. 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.
2. 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":
1.
After connecting
to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2.
Expand Databases,
and, depending on the database, either select a user database or expand System Databases and
select a system database.
3.
Right-click the
database, and then click Properties, which opens the Database
Properties dialog box.
4.
In the Select
a page pane, click Options.
5.
The current
recovery model is displayed in the Recovery model list box.
6.
Optionally, to
change the recovery model select a different model list. The choices are Full, Bulk-logged,
or Simple.
7.
Click OK.
3. 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.
4. 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.
5. Now
run a SELECT script and check the records. They would contain the values before
the Update/Delete script execution. J
