CODEWITHSUNDEEP
sql-serversql-server-2005database-backupsdatabase-restore
HasanG
HasanG

Reputation: 13161

Recover deleted record in SQL Server

I accidentally deleted a row in a table and want to restore it. I've found a solution here: How to recover deleted records in MS SQL server

I've tried to restore database from backup taken after delete. But I can't restore database with STOPAT option:

RESTORE LOG database FROM  DISK = N'X:\database.BAK' WITH
STOPAT = N'2011-02-12T00:00:00', RECOVERY

I have following error:

Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are
ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Upvotes: 3

Views: 22959

Answers (4)

Ivan Stankovic
Ivan Stankovic

Reputation: 1602

Yes, it's possible to recover deleted records from SQL Server database using various methods, which, on the other hand, requires different prerequisites. Some of them involve native SQL Server features, others 3rd party tools.

Anyway, in some cases the deleted data is not lost even your database is not using the Full recovery model. For more information, check the Recover deleted SQL data from a backup or from online database files online article.

Disclaimer: I work as a Product Support Engineer at ApexSQL

Upvotes: 1

David Smithers
David Smithers

Reputation: 2364

If your database was in full recovery mode then you can try to read transaction log and recover data from there using commercial tool such as ApexSQL Log or sql server commands such as DBCC LOG or fn_log.

Also you can try looking at these posts for more details:

How to undo a delete operation in SQL Server 2005?

How to view transaction logs in SQL Server 2008

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294247

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)

Upvotes: 8

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

Taken from this link -

A differential backup, though only containing changed data pages since the last full database backup, can only be restored in conjuntion with a full backup similar to using log backups. You don't have the ability to restore only single tables or objects, but you can restore at the database, filegroup, file, and page level. You can also restore up to a particular point in time from any of the backups (assuming you've restored the required preceding full, diff, logs).

Possibly the best solution for you in this case would be to restore the database using a different database name, then moving only the data you want into your existing database? Not sure if that's exactly what you're trying to achieve though.

Upvotes: 2

Related Questions