How to Forecast the Size of Restore of Backup in SQL Server? – Interview Question of the Week #265

Question: How to Forecast the Size of Restore of Backup in SQL Server?

Answer: Recently, I had discussed this in consulting engagement: Comprehensive Database Performance Health Check. We had a very unique scenario where we had a huge backup file of the SQL Server and we had to restore it on the server. The issue is very simple that we had a limited amount of the disk and were not sure what would happen if we try to restore backup which will eventually build a database larger than the available space.

While we were working on the server’s performance, the DBA brought up this question to me. Fortunately, the answer to this problem is very simple and I pretty much every day face this scenario.

Let us assume that we have a backup with the filename adv.bak. Let us check how much space will it contain on the drive with the help of the command RESTORE FILELISTONLY.

RESTORE FILELISTONLY
FROM DISK = N'H:\data\adw.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.

Reference: Pinal Dave (https://blog.sqlauthority.com)