Existe uma maneira de recuperar o nome do arquivo lógico de um arquivo de backup?

Retirado desta pergunta SO . Isso parece funcionar para todas as versões até 2019, inclusive.

Apenas para notar, a definição mudou duas vezes desde o SQL 2000 - eu não julgo 2 mudanças em 19 anos como uma mudança frequente pessoalmente. Nesse sentido, eu não me preocuparia em usar RESTORE FILELISTONLY desde que você use um script genérico como o abaixo.

CREATE TABLE #FileListHeaders (     
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  bit
)
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(4)) as float) > 9 -- Greater than SQL 2005 
BEGIN
    ALTER TABLE #FileListHeaders ADD TDEThumbprint  varbinary(32) NULL
END
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(2)) as float) > 12 -- Greater than 2014
BEGIN
    ALTER TABLE #FileListHeaders ADD SnapshotURL    nvarchar(360) NULL
END
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')

SELECT * FROM #FileListHeaders

DROP TABLE #FileListHeaders

Eu escrevi uma postagem no blog SQLServerScience.com que mostra como obter os detalhes que você procura e é compatível com todas as versões do SQL Server desde 2008+

Este é o código principal desse post do blog:

/*
    This script will generate a "RESTORE DATABASE" command with the correct "MOVE" clause, etc.

    By: Max Vernon
*/

SET NOCOUNT ON;
DECLARE @FileListCmd            nvarchar(max);
DECLARE @RestoreCmd             nvarchar(max);
DECLARE @cmd                    nvarchar(max);
DECLARE @BackupFile             nvarchar(max);
DECLARE @DBName                 sysname;
DECLARE @DataPath               nvarchar(260);
DECLARE @LogPath                nvarchar(260);
DECLARE @Version                decimal(10,2);
DECLARE @MaxLogicalNameLength   int;
DECLARE @MoveFiles              nvarchar(max);

SET @BackupFile     = N'D:\SQLServer\MyDatabaseBackup.bak'; --source backup file
SET @DBName         = N'MyDB'; --target database name
SET @DataPath       = N'C:\Database\Data'; --target data path
SET @LogPath        = N'C:\Database\Log'; --target log path

/* ************************************

    modify nothing below this point.

   ************************************ */
IF RIGHT(@DataPath, 1) <> '\' SET @DataPath = @DataPath + N'\';
IF RIGHT(@LogPath, 1) <> '\' SET @LogPath = @LogPath + N'\';
SET @cmd = N'';
SET @Version = CONVERT(decimal(10,2), 
    CONVERT(varchar(10), SERVERPROPERTY('ProductMajorVersion')) 
    + '.' + 
    CONVERT(varchar(10), SERVERPROPERTY('ProductMinorVersion'))
    );
IF @Version IS NULL --use ProductVersion instead
BEGIN
    DECLARE @sv varchar(10);
    SET @sv = CONVERT(varchar(10), SERVERPROPERTY('ProductVersion'));
    SET @Version = CONVERT(decimal(10,2), LEFT(@sv, CHARINDEX(N'.', @sv) + 1));
END

IF OBJECT_ID(N'tempdb..#FileList', N'U') IS NOT NULL
BEGIN
    DROP TABLE #FileList;
END
CREATE TABLE #FileList 
(
      LogicalName               sysname             NOT NULL
    , PhysicalName              varchar(255)        NOT NULL
    , [Type]                    char(1)             NOT NULL
    , FileGroupName             sysname             NULL
    , Size                      numeric(20,0)       NOT NULL
    , MaxSize                   numeric(20,0)       NOT NULL
    , FileId                    bigint              NOT NULL
    , CreateLSN                 numeric(25,0)       NOT NULL
    , DropLSN                   numeric(25,0)       NULL
    , UniqueId                  uniqueidentifier    NOT NULL
    , ReadOnlyLSN               numeric(25,0)       NULL
    , ReadWriteLSN              numeric(25,0)       NULL
    , BackupSizeInBytes         bigint              NOT NULL
    , SourceBlockSize           int                 NOT NULL
    , FileGroupId               int                 NULL
    , LogGroupGUID              uniqueidentifier    NULL
    , DifferentialBaseLSN       numeric(25,0)       NULL
    , DifferentialBaseGUID      uniqueidentifier    NOT NULL
    , IsReadOnly                bit                 NOT NULL
    , IsPresent                 bit                 NOT NULL 
);

IF @Version >= 10.5 ALTER TABLE #FileList ADD TDEThumbprint varbinary(32) NULL;
IF @Version >= 12   ALTER TABLE #FileList ADD SnapshotURL nvarchar(360) NULL;

SET @FileListCmd = N'RESTORE FILELISTONLY FROM DISK = N''' + @BackupFile + N''';';

INSERT INTO #FileList
EXEC (@FileListCmd);
SET @MaxLogicalNameLength = COALESCE((SELECT MAX(LEN(fl.LogicalName)) FROM #FileList fl), 0);
SELECT @MoveFiles = (SELECT N', MOVE N''' + fl.LogicalName + N''' ' 
    + REPLICATE(N' ', @MaxLogicalNameLength - LEN(fl.LogicalName)) 
    + N'TO N''' + CASE WHEN fl.Type = 'L' THEN @LogPath ELSE @DataPath END 
    + @DBName + N'\' + CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'System' 
                            WHEN fl.FileGroupName IS NULL THEN N'Log' 
                            ELSE fl.FileGroupName END 
    + N'\' + fl.LogicalName + CASE WHEN fl.Type = 'L' THEN N'.log' 
                                ELSE 
                                    CASE WHEN fl.FileGroupName = N'PRIMARY' THEN N'.mdf'
                                     ELSE N'.ndf' 
                                     END 
                                END + N'''
    '
FROM #FileList fl
FOR XML PATH(''));

SET @MoveFiles = REPLACE(@MoveFiles, N'&#x0D;', N'');
SET @MoveFiles = REPLACE(@MoveFiles, char(10), char(13) + char(10));
SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2);

SET @RestoreCmd = N'RESTORE DATABASE ' + @DBName + N'
FROM DISK = N''' + @BackupFile + N''' 
WITH REPLACE 
    , RECOVERY
    , STATS = 5
    ' + @MoveFiles + N';
GO';

IF LEN(@RestoreCmd) > 4000 
BEGIN
    DECLARE @CurrentLen int;
    SET @CurrentLen = 1;
    WHILE @CurrentLen <= LEN(@RestoreCmd)
    BEGIN
        PRINT SUBSTRING(@RestoreCmd, @CurrentLen, 4000);
        SET @CurrentLen = @CurrentLen + 4000;
    END
    RAISERROR (N'Output is chunked into 4,000 char pieces - look for errant line endings!', 14, 1);
END
ELSE
BEGIN
    PRINT @RestoreCmd;
END

O comando gerado RESTORE DATABASEse parece com:

RESTORE DATABASE MyDB
FROM DISK = N'D:\SQLServer\backups\MyDB.bak' 
WITH REPLACE 
    , RECOVERY
    , STATS = 5
    , MOVE N'PRIMARY' TO N'C:\Database\Data\MyDB\system\PRIMARY'
    , MOVE N'LOG'     TO N'C:\Database\Log\MyDB\Log\LOG';
GO

Esse código também foi testado na versão Linux do SQL Server 2017.

Você perguntou:

Não existe uma maneira mais simples de armazenar os resultados de uma consulta em uma variável e usá-los? Esta é uma moleza em qualquer linguagem de programação.

O requisito aqui não é adicionar um valor a uma variável. Precisamos extrair o conteúdo de um conjunto de dados díspares em uma tabela. Pode ser conceitualmente semelhante a carregar uma matriz de um objeto. No entanto, no SQL Server, a única maneira de armazenar os resultados da saída do comando, como o RESTORE HEADERONLYcomando, é primeiro inseri-lo em uma tabela e, em seguida, obter os valores específicos da tabela desejada.

Por que não usar dbatools ou sp_restoregene que estão disponíveis gratuitamente?

Ambos podem fazer o que você procura.