What does sp_restore_filelistonly do?

In SQL Server 2017, after applying Cumulative Update 7, there's a new system stored procedure in master called sp_restore_filelistonly. What's it for?

sp_restore_filelistonly is a (currently) undocumented stored procedure with two parameters:

• @device_type NVARCHAR(10) - like 'DISK'

• @backup_path NVARCHAR(360) - which needs to point to the exact backup file you want to example, not just the folder path where it lives

When you point it at a backup file, like this:

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = 'E:\MSSQL2017\Backup\StackOverflow2010.bak';

It functions like RESTORE FILELISTONLY:

RESTORE FILELISTONLY FROM DISK = 'E:\MSSQL2017\Backup\StackOverflow2010.bak' WITH FILE = 1;

You get an output set with the files included in the backup:

• LogicalName

• PhysicalName

• Type (D for full, L for log, etc)

• FileGroupName

• Size (in bytes)

• MaxSize

• FileId

• CreateLSN

• DropLSN

• UniqueID

• ReadOnlyLSN

• ReadWriteLSN

• BackupSizeInBytes - note that this is the uncompressed size, so if your backup used compression, you won't see the real file size here

• SourceBlockSize

• FileGroupId

• LogGroupGUID

• DifferentialBaseLSN

• DifferentialBaseGUID

• IsReadOnly

• IsPresent

• TDEThumbprint

• SnapshotUrl

Why would Microsoft build it? Well, if they want to let someone get a file list without running a RESTORE command. Could be a separation-of-duties thing, or could be making the automation plumbing easier for Azure Managed Instances.

StackExchange Database Administrators Q#207936, answer score: 3