bak file = 5gb, attempted restore claims its 100Gb

17 Dec 2008 5:55 PM

Scott

this is a new one on me:

SQL server 1 holds DB1 its a 100GB file approx
I back it up
Restore it on SQL server 1 as DB2
Truncate large tables
DB2 now = 5Gb approx
BACKUP. My .bak file = 5gb ish.
ZIP = 1 gb
FTP
UNZIP. My .bak file = 5gb ish
SQL SERVER 2 has 80GB disk space
Attempt to restore DB2 from my 5Gb .bak file ...

I get the message:

"Restore failed for Server #
System.Data.SQLClientError. There is insufficent free disk space on disk
volume d:\ to create database. The database requires 101503664128 additional
free bytes while only 82900180992 bytes are available."

MY .BAK FILE IS 5GB !!! how can it think i need 100GB ?

NOTE: I should probably add i just unistalled SQL express and installed SQL
standard 2005.

Thanks for any help
Scott

17 Dec 2008 6:03 PM

Chris Wood

Scott,

The database on disk is 100Gb as you mention. The actual data is now around
5Gb after the truncation. The data in the backup may only be 5Gb but it was
from a 100Gb database so the restore is trying to allocate the new database
with the attributes of the database it was backed up from.

You need to shrink the original from 100Gb and back it up again and then
try.

Chris

Show quoteHide quote

"Scott" <nospam123@yahoo.co.uk> wrote in message
news:eN9NVCHYJHA.684@TK2MSFTNGP04.phx.gbl...
> this is a new one on me:
>
> SQL server 1 holds DB1 its a 100GB file approx
> I back it up
> Restore it on SQL server 1 as DB2
> Truncate large tables
> DB2 now = 5Gb approx
> BACKUP. My .bak file = 5gb ish.
> ZIP = 1 gb
> FTP
> UNZIP. My .bak file = 5gb ish
> SQL SERVER 2 has 80GB disk space
> Attempt to restore DB2 from my 5Gb .bak file ...
>
> I get the message:
>
> "Restore failed for Server #
> System.Data.SQLClientError. There is insufficent free disk space on disk
> volume d:\ to create database. The database requires 101503664128
> additional free bytes while only 82900180992 bytes are available."
>
> MY .BAK FILE IS 5GB !!! how can it think i need 100GB ?
>
> NOTE: I should probably add i just unistalled SQL express and installed
> SQL standard 2005.
>
> Thanks for any help
> Scott
>
>

18 Dec 2008 7:53 AM

Uri Dimant

Scott
Shortly, you database contains lots of empty pages, you option is to run
DBCC SHRINKFILE/DBCC SHRINDATABASE commands

Show quoteHide quote

"Scott" <nospam123@yahoo.co.uk> wrote in message
news:eN9NVCHYJHA.684@TK2MSFTNGP04.phx.gbl...
> this is a new one on me:
>
> SQL server 1 holds DB1 its a 100GB file approx
> I back it up
> Restore it on SQL server 1 as DB2
> Truncate large tables
> DB2 now = 5Gb approx
> BACKUP. My .bak file = 5gb ish.
> ZIP = 1 gb
> FTP
> UNZIP. My .bak file = 5gb ish
> SQL SERVER 2 has 80GB disk space
> Attempt to restore DB2 from my 5Gb .bak file ...
>
> I get the message:
>
> "Restore failed for Server #
> System.Data.SQLClientError. There is insufficent free disk space on disk
> volume d:\ to create database. The database requires 101503664128
> additional free bytes while only 82900180992 bytes are available."
>
> MY .BAK FILE IS 5GB !!! how can it think i need 100GB ?
>
> NOTE: I should probably add i just unistalled SQL express and installed
> SQL standard 2005.
>
> Thanks for any help
> Scott
>
>

19 Dec 2008 10:03 AM

Scott

thanks for the reply. I understand the DB contains empty pages after the
truncation. What i dont understand is why NTFS shows 5GB and SQL thinks its
still 100GB.

cheers
scott

19 Dec 2008 12:57 PM

Dan Guzman

> thanks for the reply. I understand the DB contains empty pages after the
> truncation. What i dont understand is why NTFS shows 5GB and SQL thinks
> its still 100GB.

The database backup on disk includes only used pages, not empty ones. That
is why only 5GB is needed for the backup file. However, when you restore
the database, it is created exactly like the original 100GB database,
including both empty and used pages. Both Chris and Uri already mentioned
that you need to shrink the database file(s) before you perform the backup
to reduce space requirements for the restore.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Show quoteHide quote

"Scott" <nospam123@yahoo.co.uk> wrote in message
news:eXNfNEcYJHA.5828@TK2MSFTNGP03.phx.gbl...
> thanks for the reply. I understand the DB contains empty pages after the
> truncation. What i dont understand is why NTFS shows 5GB and SQL thinks
> its still 100GB.
>
> cheers
> scott
>