sql server backup restore multiple require disk space

Question

I took a full backup file of a live system, approximately 210 GB. It's in the downloads folder with the .bak extension. I'm trying to install it locally with the restore database in SQL Server on my desktop computer, but it gives the following error.

Yes, I do not have more than 210 GB of free space on the C disk, but it requires approximately 1.3 TB of free space from me. What is the reason for this or is there an alternative way to avoid this?

Here is the photo;

enter image description here

Real file space in average 210 GB

enter image description here

• Charlieface: Does this answer your question? Restore DB Backup with limited amount of disk space

Answer 1

The size of the backup file is not the size of how big the database (it's data and log files) is. The backup file only contains the non-empty pages of data from the database. It's also possible compression was used when the backup file was created.

If you look at the total size of the original database that the backup came from, I imagine you'll see it too is approximately 1.3 TB big. You can verify this by running the system stored procedure sp_spaceused within that database.

You need the same amount of free space available in order to restore a backup from a database of that size. There's no way around this.

It is a slight red flag that the database backup is so much smaller though. It smells like the autogrowth settings of the original database might've been set too wildly high. Or a rogue transaction blew up the Transaction Log. As it seems like you have almost a terabyte of space not actively in use (empty pages).

If it was due to improper autogrowth settings, you may want to adjust them to something more reasonable and then do a one-time shrink to release the unneeded claimed space back to the disk. (Don't get comfortable with shrinking, it's not recommended as a regular process, and only should be used for one-off special occasions.) Then when you go to restore a backup from that database, it'll be more reasonably sized.