This is a quick piece of code that searches a drive for SQL backups(or at least anything that is a *.bak format) and runs restore headeronly on it to determine what database was backed up and when. There’s nothing especially clever going on here but it took me longer than I cared to admit so I thought it was worth dropping here so I could find it next time I need to do something like this. The trickiest part of this is getting a restore headeronly output into a temp table. You have to create the table and exactly match the column names and numbers. Luckily this work was already done for me here.

SET NOCOUNT ON;

DECLARE @looper INT = 0;
CREATE TABLE #BakFiles (
    FilePath NVARCHAR(MAX)
);

INSERT INTO #BakFiles
EXEC xp_cmdshell 'dir H:\*.bak /s /b'; -- Change H:\ to your desired drive

DECLARE @FilePath NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

CREATE TABLE #BackupInfo (
    DatabaseName NVARCHAR(255),
    BackupFileLocation NVARCHAR(MAX),
    BackupStartDate DATETIME
);

DECLARE FileCursor CURSOR FOR
SELECT FilePath
FROM #BakFiles
WHERE FilePath IS NOT NULL AND FilePath <> ''; -- Filter out null or empty paths

OPEN FileCursor;

FETCH NEXT FROM FileCursor INTO @FilePath;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @looper = @looper + 1;

    SET @SQL = 'RESTORE HEADERONLY FROM DISK = N''' + @FilePath + '''';

    PRINT 'Processing file ' + CAST(@looper AS NVARCHAR(100)) + ': ' + @SQL;

    CREATE TABLE #HeaderInfo (
    BackupName nvarchar(128),
    BackupDescription nvarchar(255),
    BackupType smallint,
    ExpirationDate datetime,
    Compressed bit,
    Position smallint,
    DeviceType tinyint, 
    UserName nvarchar(128),
    ServerName nvarchar(128),
    DatabaseName nvarchar(128),
    DatabaseVersion int,
    DatabaseCreationDate datetime,
    BackupSize numeric(20, 0),
    FirstLSN numeric(25, 0),
    LastLSN numeric(25, 0),
    CheckpointLSN numeric(25, 0),
    DatabaseBackupLSN numeric(25, 0),
    BackupStartDate datetime,
    BackupFinishDate datetime,
    SortOrder smallint,
    [CodePage] smallint,
    UnicodeLocaleId int,
    UnicodeComparisonStyle int,
    CompatibilityLevel tinyint,
    SoftwareVendorId int,
    SoftwareVersionMajor int,
    SoftwareVersionMinor int,
    SoftwareVersionBuild int,
    MachineName nvarchar(128),
    Flags int,
    BindingId uniqueidentifier,
    RecoveryForkId uniqueidentifier,
    Collation nvarchar(128),
    FamilyGUID uniqueidentifier,
    HasBulkLoggedData bit,
    IsSnapshot bit,
    IsReadOnly bit,
    IsSingleUser bit,
    HasBackupChecksums bit,
    IsDamaged bit,
    BeginsLogChain bit,
    HasIncompleteMetaData bit,
    IsForceOffline bit,
    IsCopyOnly bit,
    FirstRecoveryForkID uniqueidentifier,
    ForkPointLSN numeric(25, 0),
    RecoveryModel nvarchar(60),
    DifferentialBaseLSN numeric(25, 0),
    DifferentialBaseGUID uniqueidentifier,
    BackupTypeDescription nvarchar(60),
    BackupSetGUID uniqueidentifier,
    CompressedBackupSize bigint,
    Containment tinyint,
    KeyAlgorithm nvarchar(32),
    EncryptorThumbprint varbinary(20),
    EncryptorType nvarchar(32)
);

    INSERT INTO #HeaderInfo
    EXEC sp_executesql @SQL;
	
    INSERT INTO #BackupInfo (DatabaseName, BackupFileLocation, BackupStartDate)
    SELECT DatabaseName, @FilePath, BackupStartDate
    FROM #HeaderInfo;

    DROP TABLE #HeaderInfo;

    FETCH NEXT FROM FileCursor INTO @FilePath;
END;



SELECT DatabaseName, BackupFileLocation, BackupStartDate
FROM #BackupInfo;

-- Cleanup
DROP TABLE #BakFiles;
DROP TABLE #BackupInfo;
CLOSE FileCursor;
DEALLOCATE FileCursor;