CODEWITHSUNDEEP
sql-server-2005transaction-log
Daren Thomas
Daren Thomas

Reputation: 70344

How to undo a delete operation in SQL Server 2005?

Our Test DB is suddenly missing rows. We want them back.

Is there a way to sift through everything that has happened to the database today? Each SQL statement? I presume this kind of stuff is in the transaction log, but am not sure how to view it.

Is there a way to undo delete operations?

BTW: Yes, we do have a backup, but would prefer to find the cause of the deletion as well...

Upvotes: 2

Views: 16583

Answers (5)

Jason Clark
Jason Clark

Reputation: 1423

You can undo a transaction by using Rollback command in SQL Server. But, you need to know the transaction can be rolledback if the transaction were performed within Begin transaction.

Upvotes: -1

JdMR
JdMR

Reputation: 1266

[late answer but hopefully useful]

There is a way to recover deleted rows using transaction log but only if you use 3rd party tools and only of your database is in full recovery mode.

Dell (formerly Quest) has Toad for SQL Server and ApexSQL has ApexSQL Log and ApexSQL Recover that can also read t-log and recover data. Unfortunately Log Rescue from Red Gate can only read logs on SQL Server 2000.

There is also a way to read t-log using undocumented dbcc log command. See more details here.

Considering you already have database backups you can restore these in separate database and then use one of many data comparison tools that exist on the market to insert missing data into production database. Of course this can only recover data delete prior to creating a backup.

Upvotes: 2

Gary
Gary

Reputation: 11

I'd give ChronicDB a try which has a free version rather than pay for Red Gate

http://chronicdb.com/blogs/undelete_from_whoops

Upvotes: 1

Dave Markle
Dave Markle

Reputation: 97821

You need a third-party tool to do this. The tool has to be able to go into the transaction logs and view the log entries so you can see what happened. I haven't used any of these tools, but I'd try Red Gate's SQL Log Rescue for starters. Give it a try:

http://www.red-gate.com/products/SQL_Log_Rescue/index.htm

Upvotes: 1

Galwegian
Galwegian

Reputation: 42257

You can do this with some of Red Gate's tools, but it costs. Take a look at SQL Log Rescue.

Otherwise, I'd be tempted to do a restore.

Upvotes: 1

Related Questions