HostWeb Forums » Microsoft Databases » microsoft.public.sqlserver.programming » Create backup of SQL 2005 Express, database get smaller

Topic: Create backup of SQL 2005 Express, database get smaller

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 12:43:01 PM

By: Anonymous

Hello

Have created a backup routine of my Visma databases who use SQL 2005 Express
engine, using a bat-file like this:

d:
cd\Databasebackup\Visma
del F0001bu.bak
del vbsysbu.bak
sqlcmd -S DC-Profax\Visma -i D:\Databasebackup\Vismabackup.sql

and used T-sql commands in "Vismabackup.sql" like this:

EXEC sp_addumpdevice 'disk', 'F0001bu',
'D:\Databasebackup\Visma\F0001bu.bak'
BACKUP database F0001 TO F0001bu
GO
EXEC sp_addumpdevice 'disk', 'vbsysbu',
'D:\Databasebackup\Visma\vbsysbu.bak'
BACKUP database vbsys TO vbsysbu
GO

My question is;
the backupfile seams to be a bit smaller than the original file. Looking at
the original "F0001_Data.mdf", the sice is 1 984 192 kb, and looking at the
backupfile "F0001.bak", the sice is 1 977 178 kb.
I am backing up 3 other F000x bases too, and it all get smaller.
I've not tried to restore this bases in case something is wrong, have
neither needed to do this, but wonder if this is normal.

Thanks.

Replies below ↓

Replies

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 12:49:06 PM

By: Tom Moreau

Re: Create backup of SQL 2005 Express, database get smaller

SQL Server is fairly efficient when it does a backup. It backups up only
the pages that are actually used. Therefore, if your DB size is 500MB and
your data and indexes are only 100MB, then the backup will be about 100MB.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Gunnar" <Gunnar@discussions.microsoft.com> wrote in message
news:56EEFCD6-733D-45AC-A9C5-F55501EE3269@microsoft.com...
Hello

Have created a backup routine of my Visma databases who use SQL 2005 Express
engine, using a bat-file like this:

d:
cd\Databasebackup\Visma
del F0001bu.bak
del vbsysbu.bak
sqlcmd -S DC-Profax\Visma -i D:\Databasebackup\Vismabackup.sql

and used T-sql commands in "Vismabackup.sql" like this:

EXEC sp_addumpdevice 'disk', 'F0001bu',
'D:\Databasebackup\Visma\F0001bu.bak'
BACKUP database F0001 TO F0001bu
GO
EXEC sp_addumpdevice 'disk', 'vbsysbu',
'D:\Databasebackup\Visma\vbsysbu.bak'
BACKUP database vbsys TO vbsysbu
GO

My question is;
the backupfile seams to be a bit smaller than the original file. Looking at
the original "F0001_Data.mdf", the sice is 1 984 192 kb, and looking at the
backupfile "F0001.bak", the sice is 1 977 178 kb.
I am backing up 3 other F000x bases too, and it all get smaller.
I've not tried to restore this bases in case something is wrong, have
neither needed to do this, but wonder if this is normal.

Thanks.

Reply | New Topic | This is SPAM | This is Offensive

Submitted: 6/10/2008 1:11:02 PM

By: Anonymous

Re: Create backup of SQL 2005 Express, database get smaller

So this is normal, I see.
Thanks for fast answer! Have a nice day.

"Tom Moreau" wrote:

> SQL Server is fairly efficient when it does a backup. It backups up only
> the pages that are actually used. Therefore, if your DB size is 500MB and
> your data and indexes are only 100MB, then the backup will be about 100MB.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Gunnar" <Gunnar@discussions.microsoft.com> wrote in message
> news:56EEFCD6-733D-45AC-A9C5-F55501EE3269@microsoft.com...
> Hello
>
> Have created a backup routine of my Visma databases who use SQL 2005 Express
> engine, using a bat-file like this:
>
> d:
> cd\Databasebackup\Visma
> del F0001bu.bak
> del vbsysbu.bak
> sqlcmd -S DC-Profax\Visma -i D:\Databasebackup\Vismabackup.sql
>
> and used T-sql commands in "Vismabackup.sql" like this:
>
> EXEC sp_addumpdevice 'disk', 'F0001bu',
> 'D:\Databasebackup\Visma\F0001bu.bak'
> BACKUP database F0001 TO F0001bu
> GO
> EXEC sp_addumpdevice 'disk', 'vbsysbu',
> 'D:\Databasebackup\Visma\vbsysbu.bak'
> BACKUP database vbsys TO vbsysbu
> GO
>
>
> My question is;
> the backupfile seams to be a bit smaller than the original file. Looking at
> the original "F0001_Data.mdf", the sice is 1 984 192 kb, and looking at the
> backupfile "F0001.bak", the sice is 1 977 178 kb.
> I am backing up 3 other F000x bases too, and it all get smaller.
> I've not tried to restore this bases in case something is wrong, have
> neither needed to do this, but wonder if this is normal.
>
> Thanks.
>
>
>

Page 1 of 1 Pages