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?
6
-
The DatabaseBackupLSN for a differential backup should match the CheckpointLSN of a full backup: restore headeronly from disk='MyBackupLocation\differential_backup.bak'– Mitch WheatApr 28, 2020 at 14:07
-
Can you query the msdb of the database server that crashed?– nkdbajoeApr 28, 2020 at 17:25
-
@nkdbajoe That server is completely gone. Some backups and differentials were found scattered on other servers.– RickApr 28, 2020 at 17:32
-
Do you have a backup of msdb that you could restore and review?– nkdbajoeApr 28, 2020 at 17:35
-
@nkdbajoe I found one. Not sure how old. What commands can I run?– RickApr 28, 2020 at 20:14
|
Show 1 more comment
1 Answer
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.
-
Would this work on the full backup to find where the differential backup file is located?– RickApr 28, 2020 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-DbaBackupInformationcommand assumes you've got a directory with some backups in it and it will walk through them to find what you have.– Ben ThulApr 28, 2020 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 ThulApr 28, 2020 at 19:39
-