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?

migrated from stackoverflow.com Mar 30 '16 at 13:52

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

  • Is there any way to temporarily get a drive large enough to restore the whole original-sized thing? – Peter Tirrell Mar 22 '16 at 19:21
  • Since this is a VM can you allocate an extra TB or so to allow you to restore this database? Then you can shrink it if need be and unallocate the space to your VM. Make sure you defrag indexes and such after the shrink. – Sean Lange Mar 22 '16 at 19:22
  • lol... 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 '16 at 19:28
  • 2
    I think you are out of luck then ,there is no way you can shrink while restoring – TheGameiswar Mar 22 '16 at 19:33
  • 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 '16 at 21:02
0

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.
  • 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. – Don Mar 30 '16 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

  • 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 '16 at 21:01

Your Answer

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

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