Associate BAK file with SQL Server database

Posted on

Question :

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?

Answer :

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *