0

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

Ivan Lewis
  • 740
  • 1
  • 9
  • 19
  • 5
    You’d have to restore a backup you took BEFORE you dropped the table. You also forgot to include what error you got and what command you used that produced that. – S3S Jan 15 '19 at 12:43
  • 1
    If you have transaction logs as well, then restore the Full Backup (from before the drop), and the use the transaction logs to restore to a point in time **before** you dropped the table. You might want to do some test restores on your Sandbox Server, if the table as a high amount of load, to try and get as close as possible to the time it was dropped. Obviously, if you restore to a point in time where the table doesn't exist you've gone to far, but if you're on your sandbox server, you can restore as many times as you want, to different times, without breaking anything (further). – Thom A Jan 15 '19 at 12:50
  • 1
    Note that a Full backup from **after** the `DROP` is useless here. You need a copy of the database from **before**. If you only have a copy of the Full database backup from after you made the `DROP` the table is gone and there's little to nothing you can do about it apart from creating the table again by using the definition in your source control. Any data that was in that table though is very likely lost, unless anyone personally remembers its contents. – Thom A Jan 15 '19 at 12:51
  • I didn't take a full backup before the drop. And after the drop I have 2 log backups. Also is there any third party tool can help in this case? – Ivan Lewis Jan 15 '19 at 13:20
  • 1
    So just to be clear, the backup you took AFTER you dropped the table was the first time you have ever done full backup? I only ask so that we can get a starting point to help you, and not to be critical. If that is the case then I would ask how that table was built initially and if you could retrace those steps. – JMabee Jan 15 '19 at 13:34
  • 1
    You could not have taken log back ups after the drop without first having a full back up taken at some point in time – S3S Jan 15 '19 at 13:48
  • 1. I have 4 month old full backup. 2. INSERT, UPDATE, DELETE happened on the table from the production application. 3. I dropped the table. 4. I took a full backup. 5. I followed steps given here: https://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/ 6. Auto scheduled transaction log backup happened on the server via sql agent job. Now any chances to recover the drop happened in step 3 either using sql queries or by using any third party tool? – Ivan Lewis Jan 15 '19 at 13:58
  • Do you have **every** transaction log back up taken since the Full Backup from 4 months ago? If that's on a regular interval (let's say every 15 minutes) then you'll need all (roughly) 11500 transaction log files (4 (months) * 30 (days) * 24 *(hours) * 4 (15 minute intervals)) if you're missing even one file, then (unfortunately) those transaction log backups are useless once you get to the first one that is missing. – Thom A Jan 15 '19 at 14:30
  • I have 4 month old full backup and till table drop no other backup taken. – Ivan Lewis Jan 15 '19 at 14:53
  • Ahhh, so when you ran fn_dump_dblog on your first log backup did you find the entry that shows where you dropped the table? – JMabee Jan 15 '19 at 15:03
  • Yes it showed me and I have that lsn. But after this "STOPBEFOREMARK" recovery query I m not finding that lsn in the fun_db. – Ivan Lewis Jan 15 '19 at 15:08
  • So on a separate machine, you restored the 4 month old DB, and ran the log restore up until the STOPBEFOREMARK and got this error? – JMabee Jan 15 '19 at 15:09
  • No. I didn't restore the 4 month old db. I restored the latest backup after drop to another database as given in that link. Another new database appeared and got the above error which I have quoted in my question. – Ivan Lewis Jan 15 '19 at 15:10
  • Nope. Restore the old one and run the log restore up to that LSN. The error you are getting is because the database in the full backup is beyond that point in time...if that makes sense. – JMabee Jan 15 '19 at 15:13
  • I updated my question with what I exactly did. – Ivan Lewis Jan 15 '19 at 15:24

1 Answers1

1

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.

JMabee
  • 2,230
  • 2
  • 9
  • 13
  • Please check I updated the question with the screenshots. Confirm whether your answer will me help me out. – Ivan Lewis Jan 15 '19 at 15:44
  • Correct me if I am wrong, you are trying to use your current backup and I am telling you to use an old one. – JMabee Jan 15 '19 at 15:48
  • Yes. I m trying to use current backup. Also is it possible to undo the backups (fullbackup and log backups) which are taken after the drop? Check my restore to screenshot above. Is it possible to go back to 15:00 hrs database state? – Ivan Lewis Jan 15 '19 at 15:58
  • The answer I am giving you is to use your old backup. You can try or not. The only way to undo anything is to restore an older copy, I guess that is the point of this entire question. – JMabee Jan 15 '19 at 16:15
  • By doing what you said in your answer Can I get my whole table data back? from 4 months till yesterday. – Ivan Lewis Jan 15 '19 at 16:32
  • Well I believe it will. – JMabee Jan 15 '19 at 22:24
  • Obviously restore it to another db name so you don't over write your production data – JMabee Jan 15 '19 at 22:25