Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :

TITLE: Microsoft SQL Server Management Studio

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


BUTTONS:

OK

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?

share|improve this question
add comment

migrated from stackoverflow.com Jan 24 '13 at 15:52

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
1  
why the size of the logfiles only? –  miracle173 Jan 24 '13 at 17:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.