2

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?

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

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

3

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.

1

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

  • 1
    why the size of the logfiles only? – miracle173 Jan 24 '13 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy