Database backups growing exponentially

[greggarpin]

Hi All.

I'm currently maintaining 4 servers - 1 for public/customers and 3
for backups, development, etc...
I regularly backup the entire SQL database for our public server and
restore it on each of the other servers. Lately, however, the database
backups have grown (in size) incredibly fast - they've gone from about
200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this at
first since our client traffic has drastically increased as well.) The
weird thing, though, is that (on two of the backup servers) when I
restore the backup then use those servers to create a new complete
backup, the new backup is only about 200-300 MB in size.
My assumption is that there's some kind of setting buried deep inside
the sql configuration allowing it to compress or otherwise alter
backups. Does anyone have any ideas/thoughts as to what may be causing
this issue?
We're using SQL Server 7 on Windows 2000 servers.

Thanks in advance.

Gregg
GA****@nospam.plan3D.com

[John Bell]

Hi

You are probably appending multiple backups to the same device. The INIT
keyword indicates the backup will overwrite existing backups the NOINIT
keyword indicates that the backup will be appended. See Books online for
more details.

John

<gr********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Hi All.

I'm currently maintaining 4 servers - 1 for public/customers and 3
for backups, development, etc...
I regularly backup the entire SQL database for our public server and
restore it on each of the other servers. Lately, however, the database
backups have grown (in size) incredibly fast - they've gone from about
200MB to 2+ GB in 2 months. (I wasn't entirely surprised by this at
first since our client traffic has drastically increased as well.) The
weird thing, though, is that (on two of the backup servers) when I
restore the backup then use those servers to create a new complete
backup, the new backup is only about 200-300 MB in size.
My assumption is that there's some kind of setting buried deep inside
the sql configuration allowing it to compress or otherwise alter
backups. Does anyone have any ideas/thoughts as to what may be causing
this issue?
We're using SQL Server 7 on Windows 2000 servers.

Thanks in advance.

Gregg
GA****@nospam.plan3D.com

[Byrocat]

My thoughts too!

Not sure if you're doing the backup via a batch process or the GUI.

I'm going to describe the GUI interface, since that also includes a
scheduler attribute that you may be using.

At the bottom of the "SQLServer Backup" panel, there's a section
called "Overwrite" -- set it to "Overwrite existing media" and
perform a backup.

Item to think about: it also sounds as though you're doing a complete
backup of the database each time. If you wish to retain the historical
sequence of records being added, changed and deleted; you'll need to
select "differential backup" or "transaction log", depending upon your
requirements.

The decision as to which method of backups to use depends upon the
volatility of the data and how important the historical "log" is
versus snap-shots.

Have you also thought of replication to shift the data between the
servers? You are obviously doing a one-way star arrangement (central
master and remote copies, no changes coming back) and replication is a
perfect solution to your needs.