question

SQLDBA123 avatar image
SQLDBA123 asked

Is it possible rollback data after deleting data on table of SQL Server Database ?

1. Is it possible rollback data after deleting data on SQL Server Database table ? 2. What are the options to get back the data? 3. Is there any tools to get back the data ? 4. Similar like in ORACLE Commit statement. Note: Don't say restore it another dummy database and copy it.
deletedatarollback
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
Yes, if you created an transaction that you didnt commit Begin tran Delete from table mytable Rollback tran
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

No, then you need to restore from the backup and the transaction log. (point in time restore)
4 Likes 4 ·
If we commit, we can not rollback....
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
So you're asking whether it's possible to uncommit a committed transaction? The answer is no, not with standard SQL Server tools. However, there are a couple of tools out there that may help with solving your problem: * ApexSQL Log tools - * If you're unfortunate enough for this problem to be on SQL Server 2000 32-bit, then Red-Gate used to do a tool that's now unsupported freeware - SQL Log Rescue - I take it your comment of "Don't say restore it another dummy database and copy it"[sic] means that you either don't have a backup, or you don't have any DR platform you can use to restore to. If you have a backup but no restore platform, then you might be interested in something like Red-Gate's SQL Virtual Restore. Our very own @Grant Fritchey has written an article that walks you through the process. Or Red-Gate also do an object level recovery from native backup tool:
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas, I think, recovery will be possible after commit if database is in full recovery mode. Is it right? Can these tool recovery data if database is in simple recovery mode?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.