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:

I'm confused - your screenshot has the 'size' column - is it wrong? — George.Palacios, Mar 27 '19 at 07:08
You have Size in bytes - divide by 1024 to get Kb and so on. Summarize Size of all files to get total required space. Or, maybe, you are asking something else? — Denis Rubashkin, Mar 27 '19 at 07:18
Yes, thanks for the comments, they directed me to the answer. — Marcello Miorelli, Mar 27 '19 at 07:33
This is the exact calculation: select 1468006400 /1024.0/1024.0 + 2097152000/1024.0/1024.0 that gives 3400 MB — Marcello Miorelli, Mar 27 '19 at 07:41

score 4 · Accepted Answer · answered Mar 27 '19 at 07:29

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.

+1 This answer is exactly what I was looking for, getting the results of restore filelistonly to a table. — Marcello Miorelli, Mar 27 '19 at 07:43

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

1 Answers1

Linked