Running out of disk space mid-restore

Question

A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space. He mentioned that since we are using filestream the size isn't included in the backup size.

Does this sound right? I thought the first thing sql server did up on restoring was to check the amount of disk necessary [including filestream objects] then reserve the space on disk, start initializing the allocated disk space and finally write out the data. I didn't think it was possible to run out of disk space mid-restore.

Does any of this sound right? Does anyone know of a reason the restore would fail halfway through?

Answer 1

A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space.

What was the specific error you received?

Does this [info about FILESTREAM enabled database restores] sound right?

I don't believe FILESTREAM enabled databases have different restore settings than a typical database... a quick internet search doesn't return results to the contrary.

Questions for you:

Have you checked the size of the database (link to TSQL) and compared it to how much disk space is available on the destination server's drives (another link)?

Or use this code (msdn source):
SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?

I didn't think it was possible to run out of disk space mid-restore.

It's certainly possible...

Are you actively adding any databases (or random files that shouldn't be there) to the destination drive(s)?

Is your destination drive C:?

Also, look into the use of WITH RESTART for your restores...

Answer 2

I have not tested it, but I would think the filestream file should be included in the size.

For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

Source

In addition, it sounds like you have emtpy pages.

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

Your example may not be 200+GB, but it was in the case for this person when their backup was pretty small.

Source