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.

How to Forecast the Size of Restore of Backup in SQL Server? - Interview Question of the Week #265 fortunecookie-800x582

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.

Solarwinds

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.

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

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)

Solarwinds
, , ,
Previous Post
How to Measure Resource Status on Azure? – Interview Question of the Week #264
Next Post
How to Join Two Tables Without Using Join Keywords? – Interview Question of the Week #266

Related Posts

3 Comments. Leave new

  • I like the simplicity of this article. For a newbie in SSMS, it took me a minute to figure out what “MDF” and “LDF”. I think LDF stands for “log data file” but what does “MDF” stand for. My guess would be either “master data file” or “main data file”.
    Could you please clarify? Thanks.

    Reply
  • Thank you.
    While I have you online, is there any book or course (online) that you would recommend for passing the 70-461? I have the “official” Microsoft 70-461 book, but I was told that wasn’t enough to pass the test. I have also tried some courses on Udemy and Plural-sight, but they don’t even cover as much as the book. The courses I found so far are only covering some basic T-SQL.
    Any advice would be greatly appreciated. Thanks.

    Reply

Leave a Reply

Menu