0

I cannot restore a Microsoft SQL Server database due to lack of hard drive space. The original database was 1.6TB and approximately 1.4TB was deleted from it and a backup was taken without shrinking the database.

The backup file is only 168GB and was sent to me on a hard drive through a courier. When I try to restore the 168GB backup file it tries to create the original 1.6TB .mdf data file and errors because all my available servers have less than 1TB of available disk space.

First I tried to restore the data files to a NTFS Compressed folder, but I got an error stating that the target file may not have the compression bit set.

Then, per this link: Shrink SQL database during restore

I have tried putting the restored .mdf file into a symbolic link folder which points to a compressed folder, however it errors:

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

Then, since this is running within a HYPER-V VM, I've tried adding a new virtual disk into a compressed folder, but the VM would not start complaining about one of the drives residing in a compressed folder.

Then, I tried adding a new HYPER-V virtual disk into a symbolic link folder which points to a compressed folder. This works fine, except the file is not compressed while it's restoring and was getting close to running out of space before I killed it (I'm guessing Explorer may compress the file AFTER it is closed by the writing process "SQL")

The restored database should be only 168GB of actual data within it.

Does anyone have any solution?

Improve this question
2
  • The client is leaving our company so I cannot get authorization to spend ANY money on another drive (I think it would be easy to grab a small few TB drive to stick in there just for things like this, but management is animately against spending any money) ALSO the HYPER-V host only has 2TB total and we have several VM's running on it that already consume around 1.1TB. There is not enough room on it to add another drive. Also, There are no other servers to temporarily migrate the VM's to.
    – Don
    Mar 22, 2016 at 19:28
  • Is there any way to use file compression in the target MDF file during restore? I know much of it is going to be null space.
    – Don
    Mar 22, 2016 at 21:02

3 Answers 3

Reset to default
1

Your options are pretty limited.

  1. Get enough disk space to restore this database. When restoring a backup, SQL Server needs to create the data files, even if the majority of the space is unused. After it's restored, you can shrink the files and move them, but ultimately you'll need that 1.6TB of disk space temporarily in order to perform the restore. If you are using a temporary drive just for this restore, it doesn't have to be good/fast storage--it just has to be storage.
  2. Get a new backup. You can have your client shrink the database, then take a new backup and send you a copy of the post-shrink database.
Improve this answer
1
  • 1
    I was hoping that some form of on the fly compression of either the restored data file OR of the Virtual Machine Disk file, however I could not find any combination that would work. I scrabbled together a few external USB drives onto the host machine and did a software raid 0 and put the Virtual Machine Disk file on that new software raid drive and was able to get it restored and shrunk. Thanks for your help.
    – Moon
    Mar 30, 2016 at 14:15
0

I'm not sure if this is an answer, but it's too big to comment

I had the same issue many times. In my case it always was about the log file. when you get backup, the log file may not contain any active log/data and it will not take any space in the backup file, but when restoring, SQL server tries to allocate the same space. If there is any way you can ignore log file when restoring, you might be able to restore.

check this out, it may help

Improve this answer
1
  • Thank you for the suggestion, however I know that is not the case here. I can still log into the original server over RDP and the MDF file is 1.6TB and the LDF file is only 28GB.
    – Don
    Mar 22, 2016 at 21:01
0

You can use Relocate all files to the folder where your drive has a lot of free space. I have same problem on Drive C and use Relocate all files to option in SSMS as shown as then click OK and solved. enter image description here

Improve this answer
1
  • 6 years, 4 months ago when I asked this question, I only had 1TB drives available to me. Nowadays, I would not have this issue given the ease of throwing a larger drive on the server.
    – Moon
    Jul 25, 2022 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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