2

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.

Improve this question
5
  • Have you tried restoring to a different 2012 instance where there's enough space on disk? I'm curious what the size of the database files are there. – Tara Kizer Jan 20 '17 at 15:46
  • 1
    Do you have enough free space on the drive to hold the database? AKA 10GB? If so, try setting trace flag 3105, restarting the instance and restoring again. – Sean Gallardy - Retired User Jan 20 '17 at 16:08
  • @SeanGallardy 3105 isn't in the list of allowed trace flags of the documentation. Can you elaborate? – Snowbody Jan 20 '17 at 17:24
  • 1
    It skips space checking during a restore. Since this is on a laptop, does it really matter if it isn't on the allowed list? I mean... we're splitting hairs here about allowed trace flags when it can't even restore to use it. – Sean Gallardy - Retired User Jan 20 '17 at 17:45
  • Have you considered a different approach? Could you create a new empty db and copy the data across instead of restoring from a backup? – Sir Swears-a-lot Jan 21 '17 at 4:26
1

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.

Improve this answer
2
  • That explanation makes sense, but on the source server, the files total around 10GB. See my edit. – Snowbody Jan 20 '17 at 15:13
  • Disks are cheap, but the "server" is a laptop (an Ultrabook) which is used by sales staff for demo purposes, salespeople don't want to carry external disks, and nobody wants to pull out the 500GB SSD and put in a bigger one (see: Ultrabook) – Snowbody Jan 20 '17 at 15:15

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.