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)
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.
MDF = Master Database File
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.