1

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.

enter image description here

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

enter image description here

I just found out: enter image description here

  • I'm confused - your screenshot has the 'size' column - is it wrong? – George.Palacios Mar 27 at 7: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 at 7:18
  • 3
    3.3203125*1024 = 3400.00MB - exactly the same – Denis Rubashkin Mar 27 at 7:31
  • Yes, thanks for the comments, they directed me to the answer. – Marcello Miorelli Mar 27 at 7: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 at 7:41
3

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 at 7:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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