Undelete Recently Deleted Rows SQL Server

Undelete recently deleted rows sql server

If your database is in simple recovery mode, then you are possibly out of luck. You can restore to the most recent backup, but if it was a long time ago it may not contain copies of the rows you deleted and wish to reinsert. It is also very likely that other data has been inserted in the meantime. You could restore to a new database and then do SQL surgery to get the vanished data back in.

If your database is in full recovery mode, then:

1. Find the last full backup, any incrementals since then, and all log backup files since the last incremental or full, and restore them up to the correct point in time. You could overwrite your database if that is acceptable, or you can restore to a new database and perform SQL surgery.

2. The restoration process will look something like this:

   BACKUP DATABASE YourDB TO DISK = 'D:\MSSQL\Data\YourDB\YourDB Pre-Repair.bak'
   -- It is CRUCIAL you take a new backup before doing ANYTHING so you don't
   -- make another mistake you can't reverse.
   
   RESTORE DATABASE YourDB FROM DISK
      = 'D:\MSSQL\Data\YourDB\YourDB 20121208 110000.bak' WITH REPLACE, NORECOVERY;
   -- be very careful with REPLACE as needing it proves there is un-backed-up data
   RESTORE LOG YourDB FROM DISK
      = 'D:\MSSQL\Data\YourDB\YourDB 20121208 111500.log' WITH NORECOVERY;
   RESTORE LOG YourDB FROM DISK
      = 'D:\MSSQL\Data\YourDB\YourDB 20121208 113000.log' WITH NORECOVERY;
   -- restore more log files, all of them, in order, with none skipped
   RESTORE LOG YourDB FROM DISK
      = 'D:\MSSQL\Data\YourDB\YourDB 20121209 020000.log'
      WITH STOPAT = '20121209 01:57:00', RECOVERY;
   

   Note that I used WITH STOPAT here, which allows you to restore your database up to a specific point in time. In my example, log backups were taken every 15 minutes, and the fatal query was issued at 1:57:15 AM on 2012-12-09.

If you want to restore a database to a new one, you have to do something like this:

RESTORE DATABASE NewDB FROM DISK
   = 'D:\MSSQL\Data\YourDB\YourDB 20121208 110000.bak'
WITH
   MOVE 'YourDBLogicalName' TO 'D:\MSSQL\Data\NewDB.mdf',
   MOVE 'YourDBLogicalName_Log' TO 'L:\MSSQL\Logs\NewDB.ldf';

Use RESTORE FILELISTONLY to figure out what's in the backup. If there are multiple backups in the same file, there's more syntax to read out that info and then specify which one you want to work with. Use sp_helpdb 'YourDB' to find out where to put your NewDB database and Log files.

Then there's more script to rename the logical files, if you want (which I always do).

Of course, silly me, I'm just realizing now that you could use the SSMS GUI to do most of this. But if you want to start understanding all this stuff and becoming really good at it, I recommend writing the script. I am a developer, but can restore databases lickety-split without having to ask for the "official" DBA's help.

How to recover deleted data from SQL Server?

Try this method:

https://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/

or if you have restore backup

Recover deleted record in SQL Server

I've tried to restore database from
backup taken after delete.

You cannot recover deleted records from a backup taken after the delete. You need:

• the latest full backup taken before the delete
• all the log backups taken between the last full backup until the first log backup taken one after the delete
• the database must be in full recovery mode

You may have an differential backup thrown in to reduce the log backup chain length, but this is optional.

Only if all the conditions above are satisfied, then you can go ahead and follow the procedure. If you are missing any of the above, then that record is lost. As a rule of thumb, don't follow blog articles or forum answers, including this one, follow instead the product documentation: How to: Restore to a Point in Time (Transact-SQL)

How to recover a deleted row from SQL Server 2005 table?

Rollback the transaction (if you started one).

Restore a backup (if you have one).

[edit] If you have transaction logs, you should be able to restore the backup of the database to the point roughly just before the row was deleted (assuming you know when that was).

---

Related Topics