Post reply

BAK size compared to MDF size

  • November 9, 2012 at 8:35 am

    #269994

    Hello - I'm trying to see what affects the size of a database compared to the BAK.

    I'm used to the BAK file being around 75% the size of the database but I'm working with a database where the BAK file is 8GB and when I restore it, the mdf is 36GB. There is 12 GB of unused space.

    I'm still very new at this and I'm wondering how the size works - what makes the differences between sizing of BAK files and the files when you restore.

    Can anyone point me in the right direction (articles, TSQL commands to learn, things to consider)?

    Thanks

    Dave

  • November 9, 2012 at 8:38 am

    #1557646

    Sounds like the backup is compressed.

    http://technet.microsoft.com/en-us/library/bb964719%28v=sql.100%29.aspx

  • November 9, 2012 at 8:52 am

    #1557658

    The backup is roughly the size of the data within the database. When restored, the dataase is recreated exactly *** it was at time of backup. Hence if a database has a 36 GB data file containing 8GB of data, the backup will be around 8GB, but restoring the DB will require 36 GB of space when restored.

    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

  • November 9, 2012 at 9:25 am

    #1557677

    A simple way to get a rough idea about what the .bak size will be is to check the "disk usage" report in sql 2005+, if you installed them. Space reserved is separated from space used. Data is the space used is what gets backed up. The report: SSMS-->right click DB-->reports-->standard reports. Otherwise, run sp_spaceused on the DB, the size to be backed up is the data plus index_size

Viewing 4 posts - 1 through 3 (of 3 total)

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