5

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.

3

2 Answers 2

6

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.

4
  • 1
    If you check RESTORE HEADERONLY, the output column COMPRESSED will show if the file is or not. 0=no, 1=yes.
    – user507
    Commented Sep 18, 2013 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
    Commented Sep 18, 2013 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. Commented Sep 19, 2013 at 7:03
  • Seems we had a stray Full recovery model database with a large log file. Changed to Simple and shrunk logs. Fixed.
    – PeterX
    Commented Dec 16, 2015 at 21:58
2

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.

5
  • 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 ? Commented Sep 18, 2013 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.
    – Hannah Vernon
    Commented Sep 18, 2013 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/… Commented Sep 18, 2013 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. Commented Sep 18, 2013 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. Commented Sep 19, 2013 at 2:05

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