Having the backup file, how to work out the total disk space required to do the restore?

Question

I have a nice query that gives me the date of last full backup, size and location, even when I backup to multiple backup files.

what I don't have is an estimate of the total backup size (for each of the DB files).

Basically, how much disk space in each drive I will need in order to do this restore?

I have been trying with

RESTORE FILELISTONLY FROM DISK=N'C:\SQL Server\BACKUP\my_backup_FULL_20190324_190109.bak'

but I could not work out the total disk space required for the restored-to-be database.

I got the size from the restore filelistonly above however, the size of the original db in disk is the following:

I just found out:

Answer 1

If I think you want this in a SELECT statement for multiple databases, the below code is a good start

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
    ,TDEThumbprint VARBINARY(32)
    ,SnapshotURL NVARCHAR(360)
)
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = ''C:\Program Files\Microsoft SQL Server\
       MSSQL14.MSSQLSERVER\MSSQL\Backup\test.bak''')
SELECT Size/1024/1024 AS [size in mb],* FROM #FileListHeaders

Got the script from here

One caveat is the created table differs in various versions of sql server in the above link he handles that also. change the script according to you.