1

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?

7
  • 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' Apr 13, 2020 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, 2020 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: learn.microsoft.com/en-us/sql/relational-databases/… Apr 13, 2020 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, 2020 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? Apr 13, 2020 at 20:50

2 Answers 2

1

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.

-3

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
1
  • Don't understand the negative points. If you are to vote negative, at least have the knowledge to explain why.
    – Pimenta
    Apr 28, 2020 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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