This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
UnShrunk SQL Server database restore which is larger than available disk space
Question
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?
asked 2016-03-22 by Don
Answer
Your options are pretty limited.
- 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.
- 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.
answered 2016-03-30 by Andy Mallon