Database size from Backup file

Question

Database size from Backup file

Robin35

SSCertifiable

Points: 6471
More actions
April 21, 2014 at 12:35 pm

#396635

Hello Guys,
Can anyone please help me in finding the database size from the backup file.
I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.
I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?
One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

The full backup contains the used space in the database files. The unused space isn't backed up, but it is maintained if you do a restore.

As for the size of the backup versus the size of the database, I know you can use the FILELISTONLY to get the actual size of the files in the backup file. The size column is the one you're after here. http://technet.microsoft.com/en-us/library/ms173778.aspx. The syntax is:

restore filelistonly from disk = 'x:\backup_path\backup_filename.bak';

I don't know about retrieving the size of the database that will be created, which includes the free space.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Robin35 SSCertifiable Points: 6471 More actions · Answer 2

Thanks for the explanation Wagner...

With the following query..i found the database size as 292 GB...

Restore filelistonly from disk = 'X:\xxxx.bak'

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

You can get a bunch of information from the backup files themselves, all through T-SQL. I've got an article on it over at Simple-Talk[/url].

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Grant Fritchey (4/21/2014)
You can get a bunch of information from the backup files themselves, all through T-SQL. I've got an article on it over at Simple-Talk[/url].

Thanks for posting the link to the article, Grant. I just learned something - I didn't know about LABELONLY. 🙂

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Ed Wagner (4/22/2014)
Thanks for posting the link to the article, Grant. I just learned something - I didn't know about LABELONLY. 🙂

Not a problem. That's why we're all out here helping each other.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning