CODEWITHSUNDEEP
sql-serverdata-recoverydrop-table
Ivan Lewis
Ivan Lewis

Reputation: 760

How to recover a dropped table in sql server after a full backup taken

I have to recover a dropped table from a SQL Server database which runs on SQL Server 2016 Standard edition. Database is in full recovery mode.

After the dropped the table, I took a full database backup and then transaction log backup twice. Now can I recover my dropped table with or without using expensive third party tools?

I tried this link and getting error in the last command.

Result of the STOPBEFOREMARK query:

Processed 2104752 pages for database 'databasecopy', file 'databasefilename' on file 1. Processed 6 pages for database 'datebasecopy', file 'database_log' on file 1. RESTORE DATABASE successfully processed 2104758 pages in 123.259 seconds (133.405 MB/sec). Msg 4335, Level 16, State 2, Line 15 The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point. Msg 3013, Level 16, State 1, Line 15 RESTORE LOG is terminating abnormally. RESTORE DATABASE successfully processed 0 pages in 0.544 seconds (0.000 MB/sec).

When I click the Restore option of the original database and click on Timeline option I get the below screen

enter image description here

Table dropped timing from fun_dblog:

enter image description here

Upvotes: 0

Views: 4327

Answers (1)

JMabee
JMabee

Reputation: 2300

Ok, to avoid getting into a long chat about this here is what I suggest:

  1. Restore the full backup you took 4 months ago, NOT the one you took today:

    RESTORE DATABASE [databasecopy] FROM DISK = N'OLD_BACKUP.bak' WITH MOVE N'database' TO N'C:\SQLskills\database2.mdf', MOVE N'database_log' TO N'C:\SQLskills\database2_log.ldf', REPLACE, NORECOVERY; GO

  2. Then run the RESTORE up to that LSN:

    RESTORE LOG [databasecopy] FROM DISK = N'D:\SQLskills\database_Log2.bak' WITH STOPBEFOREMARK = 'lsn:3420000002597000001', NORECOVERY; GO RESTORE DATABASE [databasecopy] WITH RECOVERY; GO

It will not work if you use your current Full Back up because everything in the log at that point has already been committed and you are trying to go back in time. Restore only goes forward in time. That is the reason for your error.

Upvotes: 1

Related Questions