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?
declare @DBname varchar(255)
declare @MDFpath varchar(255)
declare @LDFpath varchar(255)
Declare DBNames cursor for
select name from sysdatabases
fetch next from dbnames
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
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.
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)
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)
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.