database backups best practices

[xSauronx]

Hoping someone can help me out a little here.

We have several databases at work and have recently moved from tape backup to local network backups, and replicate offsite nightly. I want to optimize the bandwidth used, which means optimizing the backups.

Here is my existing scenario

each DB makes a local cold copy of a backup each night. That copy is stored on the same server hosting the DB.

Our backup software starts with a full system image, then just takes incrementals/differentials during the day and replicates at night.

This means the incrementals/differentials are also several GBs [largely due to the cold copy]. Obviously, this takes a lot of bandwidth at night and we have a 10Mb circuit between the primary site and the second site which hosts replicated backups. That may get upgraded later. Maybe.

So is it a typical/best practice to keep a cold copy on the local server? I am thinking of having the cold copy [probably 5 days worth] on the local backup server w/out replication, instead of on the local db server, and just shipping the full images and daily incrementals [without the cold copy] offsite as the DR plan to try and slim the dailies down some.

 

[razel]

Some dbs are highly compressable. Many of ours compress 6:1. Others still get to 1.5:1. However you'll need to take into account CPU vs. transfer time. It's your decision if business deems whatever costs more... CPU/man babysitting time vs. GB of storage.

In our case, buy GB tended to be always cheaper than paying operators to babysit an additional job.

 

[Jaskalas]

Move your DB to the cloud, such as AWS, and then transfer those GB sized backups in minutes, not hours.

 

[xSauronx]

Some dbs are highly compressable. Many of ours compress 6:1. Others still get to 1.5:1. However you'll need to take into account CPU vs. transfer time. It's your decision if business deems whatever costs more... CPU/man babysitting time vs. GB of storage.

In our case, buy GB tended to be always cheaper than paying operators to babysit an additional job.

ok, but do you take that compressed cold copy and keep it on the same server?
do you store it on another server/storage device on the LAN with that server?
do you replicate that copy, or a fill image of those systems, or both to a second site?

Move your DB to the cloud, such as AWS, and then transfer those GB sized backups in minutes, not hours.

i wish. we are only just now moving servers to shared colo facilities. once this hardware dies over 2 - 5 years we very well may take advantage of some cloud services instead of upgrading to new hardware [i really hope].

storage space isnt the issue so much as bandwidth right now. and bandwidth may get upgraded down the line but in the meantime i need to make the most of it while protecting our systems adequately.