Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005

    Unanswered: Why is BAK different size than MDF?

    Why are the BAK files a tad bit smaller than the MDF file?
    Why are the BAKLDF files a lot smaller than the LDF file? Am I doing something wrong in this code?

    use master
    declare @DBname varchar(255)
    declare @MDFpath varchar(255)
    declare @LDFpath varchar(255)
    Declare DBNames cursor for
    select name from sysdatabases
    open dbnames
    fetch next from dbnames
    	into @DBname
    While @@fetch_status = 0
    	set @MDFpath = '\\backupserver\ext-hdd\library\SQL_DB\' + @DBname + '.MDF.bak'
    	set @LDFpath = '\\backupserver\ext-hdd\library\SQL_DB\' + @DBname + '.LDF.bak'
    	backup database @DBname
    		to disk = @MDFpath
    		with noformat,init,skip,name = @DBname;
    	backup log @DBname
    		to disk = @LDFpath;
    	fetch next from dbnames
    	into @DBname
    close DBNames
    deallocate DBNames

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    .mdf's and .ldf's tend to be a little larger then absolutely necessary. Have a go with shrink operations if that really bothers you.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    The file size of an mdf or ldf is determined by how many pages are allocated to it.

    That size doesn't necessarily have anything to do with how much data is in it. For example, you can specify an initial size when you issue CREATE DATABASE. The resulting mdf/ldf will have the sizes you specify, but no actual data.

    I think bak files only contain the pages that have actual data written to them.

  4. #4
    Join Date
    Mar 2005
    If you're unsure about your backups, why not try a restore and a compare with your original db?

    Another thing, why do you make a backup fo the entire database (including the tx-log) followed by a backup of the log? Usually these are two separate actions, each with it's own purpose. (See the section about backup strategies in the BOL)

  5. #5
    Join Date
    Dec 2005
    Tilburg, Netherlands
    Why do you expect them to be the same size ?
    MDF file is the online data file, LDF is the online transaction log file.
    BAK file is the full backup file, TRN is the transactionlog backup.

    As Jezemine mentioned, a BAK file is allways smaller than the MDF file, as it does 'not' backup the empty parts of the MDF files. So in the wordt case these two files are about equal in size. The LDF files are some kind of format that is very well to be compressed, much better than the MDF files. These are two completely different type of files.


    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)

  6. #6
    Join Date
    May 2003
    Rochester, NY
    The MDF and LDF file sizes are dictacted by the amount of space allocated to the particular files, in saying that the files are allocated larger than the actual used space. In EM VIEW->TASKPAD for a visual display of the the files allocated size and actual use. The actual amount of space used should more acurately reflect the size of your backup files.

    Also since the log files are constantly changing size depending on recovery model, you shouldn't expect the tran log backup size to reflect the size currently being seen.

  7. #7
    Join Date
    Apr 2004
    The Netherlands

    Thank You!

    This is useful information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts