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?