Space required to restore a backup

Question

Space required to restore a backup

Stan5072

Old Hand

Points: 300
More actions
August 26, 2009 at 7:28 am

#214307

Hi there,
I've seen this message crop up a couple of times and thought nothing of it, however I'd quite like to understand the issue and how to resolve it.
I received a database backup from a client which is a modest 396meg in size, however when I attempt to restore it, it claims to require a monstrous 99gig. The drive has almost 70gig free, plenty I would have thought for restoring a database of this size.
Any help would be much appreciated.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The backup is the size of the data in the database, not the size the database was at the time of backup. To restore it, you need free space to create the data and log files the size that they were when the database was backed up.

As an example, let's say I have a database with a 100 MB data file and a 20 log file. The data file's 25% empty. My database backup will be around 75-80 MB in size, but to restore it I would need 120MB free space on the drive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rob Goddard SSCrazy Points: 2622 More actions · Answer 2

Generally the uncompressed backup size will be equivalent to the size of the used portion of the data and log files. When you restore, however, the full size of the data files will be required.

restore filelistonly from disk = 'yourbackupfile.bak'

Will tell you how big each of the files in the backup set are.

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Rob Goddard (8/26/2009)
Generally the uncompressed backup size will be equivalent to the size of the used portion of the data and log files.

Used portion of data, yes. Used portion of log, no. Full backup backs just enough of the log up to ensure that the DB can be restored consistently. It's not necessarily the entire used portion of the log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stan5072 Old Hand Points: 300 More actions · Answer 4

Thanks Gail,

Apologies for further newbish questions but I'm still struggling to get my head around the difference between the size of the data.

Might be helpful to look at an example as I can see the difference in size but can't get my head around why.

The database below has a size of 3.289 gig

Whereas the resultant .bak file from a backup with all the defaults is 2.375 gig

Although on a smaller scale, is this the same type of difference that I mentioned in my first post where the difference was 396meg and 99gig?

Also Rob I tried your command and got the error below

Msg 3201, Level 16, State 2, Line 1

Cannot open backup device 'C:\Asset Management\backup.bak'. Operating system error 3(error not found).

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

This may be in part due to me being on SQL2005, and posting in the wrong beginner section

Many thanks,

Amit Singh Ten Centuries Points: 1295 More actions · Answer 5

looks like there is definitely some free space in your data file(s) ..... when you try to restore it through GUI .... do you get to see the files that are there in the backup ??? if yes then restore filelist is not an issue .... something wrong with they way you are running the command as a query....

anyways ..... I dont think you have much choice but to get to a place where you DO have space that the backup is looking for. When you see the data files in GUI suring a restire ... you could change their location to spawn different drives and that way you could have the restore working ..... you could do this at any staging server....

Once restored ... shrink the Data and log files to release free space ..... then take another full backup of this shrinked databse and use that to restire it to your orginal intended server.

I hope this helps ....

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

matty_o_connor01 (8/26/2009)
The database below has a size of 3.289 gig
Whereas the resultant .bak file from a backup with all the defaults is 2.375 gig

So the files in total are 3.2GB, but there's free space within the data file and the total size of the data in the files is only 2.3 GB

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Asset Management\backup.bak'. Operating system error 3(error not found).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

That command is valid 2000, 2005 and 2008.

Operating system error 3 is Invalid path. Double check the path that you specified. Is it correct? Does the account that SQL server is running under have permission to read that directory?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Amit Singh (8/26/2009)
Once restored ... shrink the Data and log files to release free space ..... then take another full backup of this shrinked databse and use that to restire it to your orginal intended server

Then rebuild all indexes to undo the fragmentation that the shrink caused.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit Singh Ten Centuries Points: 1295 More actions · Answer 8

Then rebuild all indexes to undo the fragmentation that the shrink caused.

Good point ... missed that one :satisfied:

-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

Rob Goddard SSCrazy Points: 2622 More actions · Answer 9

GilaMonster (8/26/2009)
Used portion of data, yes. Used portion of log, no. Full backup backs just enough of the log up to ensure that the DB can be restored consistently. It's not necessarily the entire used portion of the log.

Thanks Gail, I wasn't aware of that at all. Seems like such an principal thing to not know as well! :crazy:

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Rob Goddard SSCrazy Points: 2622 More actions · Answer 10

Actually, of course I knew about a full backup and checkpoints in the log... What am I.. Why... - I'll get my coat.

--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Rob Goddard (9/1/2009)
Thanks Gail, I wasn't aware of that at all. Seems like such an principal thing to not know as well! :crazy:

Nah. It confuses lots of people.

http://www.sqlskills.com/blogs/paul/2007/10/25/DebunkingACoupleOfMythsAroundFullDatabaseBackups.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/More-on-how-much-transaction-log-a-full-backup-includes.aspx

Actually, of course I knew about a full backup and checkpoints in the log...

Full backups and checkpoints?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 12

So, there is no other way to just restore the data without empty file space directly from the backup?

I have the same scenario where i have database bak is 50 gb and they have allocated the data n log files in the database to be 250 GB, and when i restore it needs 250 GB and i cant restore on test with that backup because my test server has only 200GB space.

Any way to restore just the data and remove unused space in the file while restoring from the bak file?

thanks

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 13

This is what exactly i am also facing as mentioned in the below thread

http://ask.sqlservercentral.com/questions/21893/how-can-i-restore-a-database-wo-the-free-space

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 14

Short answer, natively, no. You cannot restore with less than the size that existed for the data and log files at the time of the backup.

There are third party solutions, such as Virtual Restore from Red Gate, that can help.

Disclosure: I work for Red Gate.