How to calculate the database size from a full back up (.bak) in SQL Server

Question

Is there any standard calculation to convert .bak file size to actual database size?

My .bak is a full backup, not a compressed backup. It is 40 GB in file size from Azure SQL Server. It has production data.

Any help will be appreciated from database administrators.

Answer 1

No, that is not possible. Not by looking at the file size alone.

The reason is that a backup contains the used extents (one extent is eight 8KB pages). It doesn't contain un-used extent. However, the rastore command need a database where each database file has the same file at the database files had when you produced that backup. Thos file could have bunch of un-used extents, which aren't reflected in the file size.

For example, you could have a backup file of say 10MB, but the restore command need an mdf file with 100GB file size, since that was the file size when you took the backup. I.e., the database was close to empty.

The RESTORE FILELISTONLY command will tell you the file size for each database file that the RESTORE command need to create. This is what you are looking for. For instance:

RESTORE FILELISTONLY FROM DISK = 'R:\mydb.bak'
WITH FILE = 1 

Answer 2

As Tibor mentioned you can use filelistonly to find the file size each database file that the restore command needs to create.

You could have that in a select. So you can match the drives in your server with your database files.

for this you need to enable the Ad Hoc Distributed Queries

this works on sql 2016 onwards.

SELECT 
[LogicalName]
,[PhysicalName]
,[FileGroupName]
,[Size MB]=REPLACE(CONVERT(VARCHAR(50),CAST(    CAST([Size]/1048576 as DECIMAL(12,2))     AS MONEY),1), '.00','')
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
'SET FMTONLY OFF;
 EXEC(''
 RESTORE FILELISTONLY 
 FROM DISK = ''''\\my_backup_server\master\FULL\my_db_backup.bak''''
'')
WITH RESULT SETS( 
(
                     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) NULL
                    ,SnapshotURL    nvarchar(360) NULL
))') AS a

this would give me this result:

and the sizes from the database files are: