CODEWITHSUNDEEP
sql-serversql-server-2000data-recovery
Purple Coder
Purple Coder

Reputation: 319

Recover deleted rows from SQL Server 2000

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.

Upvotes: 1

Views: 2225

Answers (3)

Ivan Stankovic
Ivan Stankovic

Reputation: 1602

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

Upvotes: 1

Purple Coder
Purple Coder

Reputation: 319

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

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

Upvotes: 0

Related Questions