5

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?

1 Answer 1

3

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:

enter image description here

  • 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.

2
  • 3
    EXEC sp_helptext 'sys.sp_restore_filelistonly' shows it's just a wrapper that executes RESTORE FILELISTONLY with either the URL or DISK keyword depending on the specified @device_type`.
    – Dan Guzman
    May 26, 2018 at 11:32
  • Perhaps they wanted to balance the sp_attach_db, as a wrapper for CREATE DATABASE --- FOR ATTACH. I.e., have use use the sp, then remove it at some later point so we need to re-write our scripts - job security for the DBAs. May 28, 2018 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.