Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Rollback the delete 2
- Thread starter manmaria
- Start date
-
1
- #2
tlbroadbent
MIS
You can only ROLLBACK data if you performed the delete in a transaction and have not committed the transaction.
If your database is in FULL or BULK-LOGGED recovery mode, you have a few recovery options.
1- If there is no danger of data loss (i.e., no rows have been updated since the delete), you could backup the log and restore the database to a point-in-time prior to the delete.
2- You can back up the transaction log, restore the database as another database to a point-in-time prior to the delete, and then compare the tables to find the records that have been deleted.
3- You could buy software such as Lumigent Log Explorer that can identify transactions and generate recovery scripts.
If the database is in SIMPLE recovery mode and you have a recent full backup, you may be able to restore that backup as another database and compare the tables to identify the deleted rows. This will not work if the rows were inserted or updated since the last backup.
Terry L. Broadbent - DBA
"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)
If your database is in FULL or BULK-LOGGED recovery mode, you have a few recovery options.
1- If there is no danger of data loss (i.e., no rows have been updated since the delete), you could backup the log and restore the database to a point-in-time prior to the delete.
2- You can back up the transaction log, restore the database as another database to a point-in-time prior to the delete, and then compare the tables to find the records that have been deleted.
3- You could buy software such as Lumigent Log Explorer that can identify transactions and generate recovery scripts.
If the database is in SIMPLE recovery mode and you have a recent full backup, you may be able to restore that backup as another database and compare the tables to identify the deleted rows. This will not work if the rows were inserted or updated since the last backup.
Terry L. Broadbent - DBA
"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)
Terry has given you most of the best options you have.
If you have audit tables on your database, you can also usually get the data back from there.
If you have a recent version of the table in dev or qa, you might be able to find the differences from there but only if the table is one that is not highly transactional like a lookup table.
"NOTHING is more important in a database than integrity." ESquared
If you have audit tables on your database, you can also usually get the data back from there.
If you have a recent version of the table in dev or qa, you might be able to find the differences from there but only if the table is one that is not highly transactional like a lookup table.
"NOTHING is more important in a database than integrity." ESquared
-
1
- #4
well, mount the most recent backup of the database on the server, then do a left join:
Select * from backup..tablename as bk
left join dbname..tablename as prd
On bk.primarykey=prd.primarykey
Where prd.primarykey is null
Of course, this only works if you inserted the records prior to backup time and deleted them after backup ...
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
Daniel Vlas
Systems Consultant
Select * from backup..tablename as bk
left join dbname..tablename as prd
On bk.primarykey=prd.primarykey
Where prd.primarykey is null
Of course, this only works if you inserted the records prior to backup time and deleted them after backup ...
Daniel Vlas
Systems Consultant
- Status
Similar threads
- Locked
- Question
- Locked
- Question