Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs?
I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs?

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do
ROLLBACK.
Are you performing regular transaction log backups? Is database in full or b
ulk logged recovery
mode? If answers to these questions are "no", you will probably need to go t
o your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in
better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

recovery from a delete statement

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'Robert wrote:
> I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'
You'll have to use a log reader tool from Lumigent or LogPi. Otherwise,
you need to restore from a backup. If the table is static, you may be
able to restore the database to another (test) server and use DTS to
copy the table.
Some backup/recovery tools like our LiteSpeed product offer object level
recovery.
David Gugick
Imceda Software
www.imceda.com|||Restore a backup to a temporary database, then copy over the missing
data.
Too late now but it's a good idea to bracket your ad-hoc DML statements
with BEGIN TRAN ... ROLLBACK TRAN until you are sure that the
modification is correct. Also a good idea to have a current backup of
course :-)
David Portas
SQL Server MVP
--|||On Thu, 17 Feb 2005 06:55:14 -0800, Robert wrote:

>I ran some delete statements in Query Analyzer and I
>found out it was the wrong database. Can you rollback the
>delete statements and recover the data from the
>transaction logs'
Hi Robert,
You can't rollback after committing the transaction.
Besides the options posted by David and David, the might be one more
option for you: If your database is in full recovery mode, you can use
point in time recovery to restore yoour database to the point in time just
before you ran the unintended deletes. This will of course destroy ALL
modifications since that time, so it may or may not be usefull.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Might not add to the posts already posted, but I have an article about this
scenario at
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:294701c51500$b01e3480$a501280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

records wont delete

I have some records that will not delete, whenever I run a delete
statement in the Query analyzer, it never completes the statement, and
I am only deleting one record at a time. Can anyone tell me why a
record wouldn't delete?[posted and mailed, please reply in news]

alloowishus (alloowishus@.yahoo.com) writes:
> I have some records that will not delete, whenever I run a delete
> statement in the Query analyzer, it never completes the statement, and
> I am only deleting one record at a time. Can anyone tell me why a
> record wouldn't delete?

There are several possible reasons:

o The row you are trying to delete is locked by another process.
o There is a foreign-key constraint from a referencing table, and that
table is large, but the referencing column is not indexed, so
checking whether the row is deletable takes a very long time.
o A variation of the above, but the foreign-key relationship uses
cascading updates.
o The table has a DELETE trigger which takes a very long time to
execute.

To check for locking, issue an sp_who from another connection. If you see
a non-zero value in the Blk column, the process on that row is blocked by
the process in the Blk column.

To check the other, use sp_helpconstraint and sp_helptrigger to look
for constraints and triggers.

The above list is not necessarily exhaustive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp