MS SQL Server - MDF vs BAK file sizes

Author

MDF vs BAK file sizes

fixitchris

2006-12-13, 7:12 pm

Why are MDF and BAK file sizes a little bit different?
Why are the backups of LDF files A LOT different?

here is the script:

use master
go

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
Begin

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,n
ame = @DBname;

backup log @DBname
to disk = @LDFpath;

fetch next from dbnames
into @DBname

end

close DBNames
deallocate DBNames

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200612/1

Andrew J. Kelly

2006-12-13, 7:12 pm

You are only including the actual data not the file in the backup. It
retains the meta data to recreate the file in total but it includes the
actual data. The data files may be much larger than the amount of data in
the files.

--
Andrew J. Kelly SQL MVP

"fixitchris" <u28526@uwe> wrote in message news:6a9e8588834e1@u
we...
> Why are MDF and BAK file sizes a little bit different?
> Why are the backups of LDF files A LOT different?
>
> here is the script:
>
> use master
> go
>
> 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
> Begin
>
> 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,n
ame = @DBname;
>
> backup log @DBname
> to disk = @LDFpath;
>
> fetch next from dbnames
> into @DBname
>
> end
>
>
> close DBNames
> deallocate DBNames
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

Hari Prasad

2006-12-13, 7:12 pm

Hello,

Primamararly we can not compare the BAK files with MDF and LDF. MDF and LDF
files are live and are used by the online database files; where as BAK files
are just the copies of MDF and LDF files which contains the utilized part of
the MDF and LDF file. Mostly to overcome the autogrowth we will give more
room to MDF and
LDF file. But in the BAK files only the data / objects utilized part of the
MDF / LDF will be taken.

Thanks
Hari

"fixitchris" <u28526@uwe> wrote in message news:6a9e8588834e1@u
we...
> Why are MDF and BAK file sizes a little bit different?
> Why are the backups of LDF files A LOT different?
>
> here is the script:
>
> use master
> go
>
> 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
> Begin
>
> 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,n
ame = @DBname;
>
> backup log @DBname
> to disk = @LDFpath;
>
> fetch next from dbnames
> into @DBname
>
> end
>
>
> close DBNames
> deallocate DBNames
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200612/1
>

Sponsored Links