I am using sql server 2008. I have taken full backup of my database. Its backup file file (.bak) is around 750 MB. Sql Server is installed on c drive which contains 5 GB free space.
When i try to restore my database it get following error :
Restore failed for Server 'FQA2008'.
(Microsoft.SqlServer.SmoExtended)ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: There is insufficient free space on
disk volume 'C:\' to create the database. The database requires
12304515072 additional free bytes, while only 5931470848 bytes are
available. (Microsoft.SqlServer.Smo)For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
Question: I dont understand that my bak file size is just 750 MB and how does it takes 12 GB of space for restoring database?
Best Answer
A few points to consider - some may apply in your case:
SQL Server backups only contain pages used
From Books Online:
This means that if your database has a lot of free space, the backup might be small.
You can check space usage using
sp_spaceused
or a view likesys.master_files
.Database restores recreate the data files exactly as they were backed up
Say you have a database with a 10GB data file and a 5GB log file, which you've backed up. When you restore the database, it will recreate these files as they were when they were backed up; it won't change the data layout in any way.
So it seems like your original database had a 12GB file - when restoring this file is being recreated.
Compression can be applied
Depending on your version/edition you can compress backup files (Enterprise only in SQL Server 2008) - this reduces the backup file size so might be misleading.
What can you do?
Easiest answer is to increase the size of your drive or restore to a different location.
Another option would be to look at the size of the original DB and consider if it has been maintained correctly, e.g. it may have a large transaction log file that hasn't been backed up correctly. Questions about appropriate database size and actions might be best suited to dba.stackexchange.com.