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.

This question already has an answer here:

I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error:

System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'c:\' to create the database. The database requires 84260749312 additional free bytes, while only 47428677632 bytes are available. (Microsoft.SqlServer.SmoExtended)

My .bak file is 8.27 gb and my C disk has 44.1 gb free space. Why do i take this error ? how can i fix this error ? Thanks in advance.

share|improve this question

marked as duplicate by Jon Seigel, Max Vernon, Paul White, StanleyJohns, dezso Sep 19 '13 at 5:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
If you right-click on the drive name in windows explorer is the drive FAT32 or NTFS? –  dcaswell Sep 18 '13 at 18:10
    
C drive is NTFS –  rockenpeace Sep 18 '13 at 18:16

2 Answers 2

As Max Vernon states, the database is trying to restore to a location that doesn't have space to contain it. If the backup file is compressed or mostly empty, you will still need the full uncompressed space to restore to. Additionally, by default SQL Server will try to restore those files to their original file location, so if that original location does not exist or does not have enough space when you restore, the restore will fail.

In order to see the space and location required for a backup, you can run the following command:

RESTORE FILELISTONLY
FROM DISK='<backup file location>'

The output from this will show all files contained within the backup. Look at PhysicalName and Size for the restore location and size (in bytes) that SQL Server needs for the restore. You can change the restore location by specifying the WITH MOVE syntax in your restore statement, but as Max states, there's not much you can do about the space required.

share|improve this answer
1  
If you check RESTORE HEADERONLY, the output column COMPRESSED will show if the file is or not. 0=no, 1=yes. –  Shawn Melton Sep 18 '13 at 19:40
    
Definitely leverage RESTORE HEADERONLY, but it will only show you the database size as a whole and not show you the individual file locations and sizes. Something to keep in mind. –  Mike Fal Sep 18 '13 at 19:45
    
my query: 'RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Backup\bilfen_ynt_backup_201309050200.bak' but i take this error: Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Backup\bilfen_ynt_backup_201309050200.bak'. Operating system error 32(Dosya başka bir işlem tarafından kullanıldığından bu işlem dosyaya erişemiyor.). Msg 3013, Level 16, State 1, Line 1 RESTORE FILELIST is terminating abnormally. –  rockenpeace Sep 19 '13 at 7:03

You need 84,260,749,312 bytes of free space to create the files necessary to restore the database. The backup file is not necessarily representative of the required size.

You can only restore this backup to a drive with the required free space.

share|improve this answer
    
but my .bak file is 8.5 gb and my c disk has sufficient disk space for .bak file. why does it want much more disk space ? –  rockenpeace Sep 18 '13 at 17:45
    
The backup file maybe compressed, or the original database files have their "initial size" property set to require the 40+ GB of disk space. –  Max Vernon Sep 18 '13 at 17:47
2  
If you have a large amount of allocated space in your DB files (even if the actual data is only 8gb), you need that same amount of space available when you do the restore. There's one exception to this rule, but it isn't pretty: ariely.info/Blog/tabid/83/EntryId/118/… –  Eric Higgins Sep 18 '13 at 18:00
    
i took this .bak file from sql server management studio of a server and this backup file is restored in my other notebook without any problem. but it does not work for my current computer. –  rockenpeace Sep 18 '13 at 18:02
2  
You are correct, Rockenpeace, it does not work. The size of the backup set is irrelevant here - only the size of the soruce database matters. To restore that backup you need a drive on your computer with at least 84.2 GB of free space. Alternatively, you could go to the source server, shrink the database, then make another backup. –  Greenstone Walker Sep 19 '13 at 2:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.