Sql-server – Make a newer version of the LDF-file work with an older version of the MDF-file

sql server

Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF? Lets assume there has been no log-backups in the time between as I guess that would make this truly impossible.

Steps:

  1. Database in full recovery mode
  2. Take a full backup
  3. Make some changes
  4. Detach database
  5. Make a filecopy of the LDF-file
  6. Restore from full backup in step 2
  7. Detach database
  8. Make a filecopy of the MDF-file
  9. Throw away the backup (important step)
  10. Delete the database
  11. Somehow make the MDF and LDF play together with the change made in
    step 3 still in place.

Just attaching the MDF and LDF gives an error:

The log scan number (38:195:41) passed to log scan database 'mic' is
not valid.

I also tried a sequence of

  1. Create database
  2. Take database offline
  3. Replace the MDF and LDF files
  4. Bring online
  5. Do a log backup
  6. Drop database
  7. Create database with attach MDF-file only
  8. Make full backup
  9. Restore full backup (norecovery)
  10. Restore log-backup from Step 5

I have a vivid memory that this worked just fine for a while but then it did not any more.

The log in this backup set terminates at LSN 38000000021300001, which
is too early to apply to the database. A more recent log backup that
includes LSN 39000000006500001 can be restored.

Best Answer

Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF?

Assuming you want the database to be transactionally consistent, no.

Just attaching the MDF and LDF gives an error

In this case it's because the log number is no valid, for whatever reason given the database file's metadata. This is a symptom of the exact process you've described. In this case it'd just be better to use the create database to regenerate the log (or DBCC commands) because either way the database isn't going to be consistent which it already wouldn't be with the above steps you've outlined. Six of one, half dozen the other.

Related Question