469,909 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Database backups growing exponentially

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

Jul 23 '05 #1
2 2193
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

Jul 23 '05 #2
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.
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Edwinah63 | last post: by
1 post views Thread by Andre | last post: by
346 posts views Thread by rkusenet | last post: by
2 posts views Thread by Alex | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.