Recover deleted rows from SQL Server 2000

Question

A script accidentally deleted few rows from the database with the following query:

DELETE FROM that_table WHERE id IN (100, 101, 102)

More rows have been added in the mean time. Is there a way for me to recover the three rows into the same table (or perhaps another database/table)? I do not want to restore the database as if this means I lose all data added/updated after that query. Hand entering the missing rows is an option.

Restore the database with another name, then copy over the data with a cross-database query. — RB.
– RB., Commented Oct 3, 2011 at 7:54
@rb. I have a backup that was created before the deletion so (i) how do I do that (ii) is it safe; I mean are there any gotchas? — Purple Coder
– Purple Coder, Commented Oct 3, 2011 at 7:58
Since you need to restore the whole data a second time (under a different name), you need extra disk space - but that's about all there is to this .... — marc_s
– marc_s, Commented Oct 3, 2011 at 8:34
And regarding gotchas - depends how skillfull you are (BIT gotcha here) and how well you plan it (another big one) and how complicated the application is - sometimes teh app has some gotchas build in. — TomTom
– TomTom, Commented Oct 3, 2011 at 8:41
@marc_s: the database is ~4GB and I have 1.2TB free, so I'm OK, right? — Purple Coder
– Purple Coder, Commented Oct 3, 2011 at 13:17

Martin Smith · Accepted Answer · 2011-10-03 10:54:13Z

0

As you are on SQL Server 2000 you may be in luck as the free Redgate SQL Log Rescue Tool works against this version.

answered Oct 3, 2011 at 10:54

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Purple Coder · Accepted Answer · 2011-10-03 13:15:34Z

Luckily I had a backup so I used the procedure described here to restore the backup on the same server but under a new database:

Restoring a Complete Backup to a New Database on the Same Server

After the database backup was restored in a new database, I located the rows and inserted them:

INSERT INTO that_database..that_table SELECT * FROM copy_database..that_table WHERE id IN (100, 101, 102)

The above query did not run out of the box, I had to enable identity insert and disable a couple of constraints temporarily.

Ivan Stankovic · Accepted Answer · 2014-06-20 20:06:53Z

You can use ApexSQL Log, a SQL Server auditing and recovery tool which reads information from database transaction logs and provides undo T-SQL scripts both for DML and DDL operations.

Disclaimer: I work as a Product Support Engineer at ApexSQL

Collectives™ on Stack Overflow

Recover deleted rows from SQL Server 2000

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related