Forecast: Check the size of the backup file before Restore the Backup in SQL Server

If we have limited amount of the disk and not sure, what would happen if we try to restore backup which will eventually build a database larger than the available space.

Let us check, how much space will it contain on the drive with the help of the command 

RESTORE FILELISTONLY.

1 2	RESTORE FILELISTONLY FROM DISK = N'E:\mydata\db_backup\test_DB.bak';

Once you run the command it will display the details of the MDF, LDF or any other file contained in the backup. Pay attention to the column Size. This column actually contains the size of the file in bytes once the backup is restored on the database.

In our example, if you notice there are two different files MDF and LDF. The respective sizes in the bytes are as following:

• MDF: 1216348160
• LDF: 679477248

Now you can covert the same using the following formula:

• 1 byte = 8 bits
• 1 kilobyte (K / Kb) = 2^10 bytes = 1,024 bytes
• 1 megabyte (M / MB) = 2^20 bytes = 1,048,576 bytes
• 1 gigabyte (G / GB) = 2^30 bytes = 1,073,741,824 bytes
• 1 terabyte (T / TB) = 2^40 bytes = 1,099,511,627,776 bytes
• 1 petabyte (P / PB) = 2^50 bytes = 1,125,899,906,842,624 bytes
• 1 exabyte (E / EB) = 2^60 bytes = 1,152,921,504,606,846,976 bytes

Or just use Google Calculator (which I do most of the time), which also displays the formula divide the digital storage value by 1e+9.

• MDF: 1216348160 = 1.21 GB
• LDF: 679477248 = 0.67 GB

Essentially, I will need around 2 GB of free space if I want to restore my backup. Well, that’s it, sometimes a complex looking problem of how to forecast the Size of Restore has a very simple solution.