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'

Record lost with UPDATE statements

Hi,
I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.

I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements.

But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
(seems like 10-20 records were deleted) , and all data rows in
TABLE_DETAILS were updated correctly (even details of lost rows of
TABLE_HEAD).

In update procedure, I update both part of primary key and other
columns with having WHERE condition.

Please help , I really don't know why this happens.

Thanks in advance
Nipon WongtrakulHave you checked if there are any triggers on the table you are updating?
Test any trigger code to see if it is handling updates of the primary key
columns correctly.

--
David Portas
SQL Server MVP
--|||Hi

Using a surrogate key will remove the need to update the details table.
Posting DDL (Create table etc) and example data (as insert statements) along
with the statements you are using may help to highlight other problems.

Also once using profiler may show something being missed.

John

"Nipon" <niponw@.yahoo.com> wrote in message
news:4c537316.0406141729.6e3cee68@.posting.google.c om...
> Hi,
> I am using MS SQL Server 7.0 SP2 in Windows 2000 server SP4.
> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
> going to update by using a stored procedure with UPDATE statements.
> But somehow ,ONCE IN A WHILE, when executing the stored procedure
> with about 1000 rows updated, I lost 10-20 records from TABLE_HEAD
> (seems like 10-20 records were deleted) , and all data rows in
> TABLE_DETAILS were updated correctly (even details of lost rows of
> TABLE_HEAD).
> In update procedure, I update both part of primary key and other
> columns with having WHERE condition.
> Please help , I really don't know why this happens.
> Thanks in advance
> Nipon Wongtrakul|||>> I have one-to-many tables (TABLE_HEAD and TABLE_DETAILS)which I am
going to update by using a stored procedure with UPDATE statements. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

>> But somehow ,ONCE IN A WHILE, when executing the stored procedure
with about 1000 rows updated, I lost 10-20 records [sic] from
TABLE_HEAD (seems like 10-20 records [sic] were deleted), and all data
rows in TABLE_DETAILS were updated correctly (even details of lost
rows of TABLE_HEAD). <<

If there is no header for a set of details, then the ON DELETE CASCADE
should have removed them for you. Likewise, the ON UPDATE CASCADE
action should have done some of the work for you between the PK-FK.

>> In update procedure, I update both part of primary key and other
columns with having WHERE condition. <<

We need to see code to debug it. It could be:

1) If you use a locator like IDENTITY as a key, and then update the
natural key, you can get the relationships out of synch.

2) There is a TRIGGER doing strange things.

3) The updates are not in the same transaction

4) The UPDATE has a FROM or other proprietary clause that does strange
things.

5) Something else.