We have a legacy database in MS SQL Server 2005. It has the latest service pack, so the backup format is compatible with 2012. The database is around 9GB, nearly all of which is data (the transaction log has been backed up). The database backup is 9.1 GB. When we transfer the backup to the 2012 machine, and try to restore, it fails saying that there is insufficient disk space available. It says that 60 GB will be required to complete the restore.
Why is this? Does it have to make 6 temporary copies in order to migrate from 2005? Will it keep on using 60 GB after it is restored? Is there some way to do the restore without freeing up 60GB on the server?
Here is the output of sp_helpfile:
STARS_Data 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS.mdf PRIMARY 9674880 KB Unlimited 10% data only
STARS_Log 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS_1.LDF NULL 299200 KB Unlimited 10% log only
Note that the database only uses around 10GB on the 2005 server.
I backed up to a completely new file. There is only one backup in the file; it was not appended. There is no problem restoring to 2005: it doesn't use vast amounts of additional space.
The hard drive has 20GB free.
Best Answer
The backup size will be around the same size as the used space in the data file if you aren't using backup compression.
The database files total 60GB on the source server, so that's what's needed on the server where you are doing a restore. While only 9GB is in use, the file size is 60GB.
You could shrink the files down on the source server and then back it up, but shrinks are bad for performance reasons. Instead, free up some space on the restore server or add more space. Disks are cheap.