One of our users deleted a lot of data from one of our database applications, and I need to find a way to restore it. I have a bak file and full recovery mode, but there is a catch.

This bak file is not recognized as belonging to this database, so I am not given the option of a timeline restore. I can restore the bak as is, but it does not contain all of the data I need. My only other option seems to be somewhat expensive (~$2000) recovery tools which can recover the data from the transaction logs.

Is there a way to convince SQL Server that this bak file really does belong to this database?

Do I have any other options?

  • When you talk about the bak file, you are refering to the full and/or the log backup ? If you do have the full and log backup, you can restore the backup to any point in time. (I recommand you restore the backup into a different SQL instance first just to make sure your backup is valid). Use TSQL like : RESTORE DATABASE [dbname] from disk=N'your full backup path' with norecovery to restore your full backup and then RESTORE LOG [dbname] from disk=N'your path to log backup' with recovery, stopat='the date' – Dominique Boucher Apr 13 at 19:13
  • @DominiqueBoucher I may be misunderstanding how this all works. My division does not have a true dba. What I have is the stale bak file from which this database was initially restored, but I have large amounts of data in the LDF. It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way? – Daniel Apr 13 at 19:20
  • You can't use the data in the LDF in a restore sequence until you take a log backup. See Tail Log Backup: docs.microsoft.com/en-us/sql/relational-databases/… – David Browne - Microsoft Apr 13 at 19:44
  • 1
    If the $2000 tool to recover the data is considered "expensive", what is the data worth to the organization? – alroc Apr 13 at 19:46
  • Was this database restored itself prior to the user operations that you now wish to roll back? For instance, was the database restored itself from an external backup (e.g. a backup taken on a different server) and then only tlog backups were taken since? – John Eisbrener Apr 13 at 20:50

This bak file is not recognized as belonging to this database

It may just be that you haven't performed a tail log backup on the target database. But you should restore the backup and all the log files to a new database first to determine if it has the data you need. Then you can either proceed with the restore, or manually copy the data from the new database.

It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way?

No. The LDF will be overwritten in a restore. You must move the transaction log records from the LDF to a log backup first. That's why RESTORE over an existing database will fail if you haven't taken a Tail Log backup: otherwise you would lose data.

So before you start the restore sequence, ensure you've taken a Log Backup of the database. This will move all the log records currently in the .LDF file into the log backup file and make them available for use in a point-in-time restore.

| improve this answer | |

Use the BAK file to create a new database. Copy all the LDF before using any procedure to ensure you don't loose any data.

Here is a link that might help understand the issue What is the LDF file in SQL Server?

You can also try to read/recover from the LDF which might be tricky - I take no responsibility on using any of the procedures or tools in these links:

  1. Reading the LDF Reading the transaction log in SQL Server – from hacks to solutions
  2. Another example with a tool How to Read Data From LDF Files in SQL Server
  3. A video example How to display and recover data from SQL log file
| improve this answer | |
  • Don't understand the negative points. If you are to vote negative, at least have the knowledge to explain why. – Pimenta Apr 28 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.