what is relation between sql server back file size and space required for restoring database

Question

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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

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?

Answer 1

A few points to consider - some may apply in your case:

SQL Server backups only contain pages used

From Books Online:

The backup contains only the actual data in the database and not any unused space.

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 like sys.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.

Answer 2

Try doing a RESTORE FILELISTONLY to see what the size of the logfile is

http://msdn.microsoft.com/en-us/library/ms173778(v=sql.100).aspx