Recover deleted record in SQL Server

sql-server sql-server-2005 database-backups database-restore
HasanG picture HasanG · Feb 14, 2011 · Viewed 18.6k times · Source

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.

Answer

Remus Rusanu picture Remus Rusanu · Feb 14, 2011

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)

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More