We had a SQL Server crash and most of the data is unobtainable. I have found some old full backups and some differential backups in different servers.

Is there a way to tell which differential belongs to which full backup?

  • The DatabaseBackupLSN for a differential backup should match the CheckpointLSN of a full backup: restore headeronly from disk='MyBackupLocation\differential_backup.bak' – Mitch Wheat Apr 28 at 14:07
  • Can you query the msdb of the database server that crashed? – nkdbajoe Apr 28 at 17:25
  • @nkdbajoe That server is completely gone. Some backups and differentials were found scattered on other servers. – Rick Apr 28 at 17:32
  • Do you have a backup of msdb that you could restore and review? – nkdbajoe Apr 28 at 17:35
  • @nkdbajoe I found one. Not sure how old. What commands can I run? – Rick Apr 28 at 20:14

From your description, you have a set of files some of which are full backups and some of which are differentials. If it were me, I'd use the various backup metadata interrogation commands inside the dbatools powershell module. Something like:

$bh = Get-DbaBackupInformation -SqlInstance yourRestoreServer -Path directoryForYourBackups;
$bh | Restore-DbaDatabase -SqlInstance yourRestoreServer -OutputScriptOnly;

That first command will go through and do a restore headeronly on all of the files and the second should select the most recent one(s) to do a restore on (which includes matching differential backups to full if that's an option).

If you don't already have the dbatools module installed, check out the installation instructions.

| improve this answer | |
  • Would this work on the full backup to find where the differential backup file is located? – Rick Apr 28 at 17:13
  • No. In order for this to be possible, a differential backup would have to write data to a pre-existing full backup. Which doesn't happen. The Get-DbaBackupInformation command assumes you've got a directory with some backups in it and it will walk through them to find what you have. – Ben Thul Apr 28 at 19:34
  • That sounds like you've got an SPN issue and I would expect that any attempt to connect to the server with Windows authentication would fail. – Ben Thul Apr 28 at 19:39
  • OK got it to work... Analyzing the data... – Rick Apr 28 at 20:21

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.