I noticed that I have some static databases that dont normally change,
so I dont want to back it up if it has not changed, but when it does,
then I want a backup.
Is there something in the master table, as example, that I can check
prior to running the backup that will indicate any changes?
An example is the Northwind database. I could exclude it from the
backup, but then I would not back it up if it where to change. Again
this is an example, I would not need to modify Northwind.
Thanks in advance for any ideas; they usually give me ideas to problems
yet to come...
Rob CamardaI don't think there's a generic way to tell if a database has changed
or not (assuming that you mean when data in a user table has changed).
You could run a trace which looks for INSERT/UPDATE/DELETE statements,
log the trace in a table, and then check the contents of the table in a
custom backup job, but that doesn't seem very practical.
If your concern is to reduce disk space used for backups, you could use
differential backups for your 'static' databases, and only do the full
backup once a month, or whatever interval is appropriate. But if your
concern is to simplify administration, and given how cheap disks are
relative to a DBA's time, I would consider just adding another disk and
continuing with the same backup job for all databases.
If this isn't helpful, you might want to give some more details about
your environment, especially about how big the databases are, how often
you expect updates, and what you're trying to achieve (eg. save disk
space).
Simon|||I use SQLsafe to backup and compress my data, which in turn is saved to
my backup server. My tape is HP's Surestore 6/6000 library. I have
5540GB tapes in the library.
Monday through Saturday I perform a differential backup and a full
backup on Sunday. My plan is to keep a 1 month worth of data on the
shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
Once the full backup is a month old, I move it to the library and
delete the files from the disk once the backup is successful. I wont
backup the diffs to tape and will delete the prior weeks diff backups
once the full backup is complete.
This is my current plan, it will change as I get useful input and see
how it works in practice.
My full backup of some tables are not currently large; the largest
5.3GB after compression. However, I tend to look forward, and forsee
more demand on the backup server, so why backup data that hasnt
changed? The library will be backing up 3 Linux servers, 16 windows
servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
maximize the storage on the Library by keeping unnecessary data off the
library.
Again, there may not be a practical solution to my question, but often
I find I learn something unrelated that may help me with a future
problem.|||Hi
If you don't back up the database(s) then the last good backup may fall off
the tape cycle. If you chose to do this then restoring the database(s) will
you having to search more tapes for the relevant backups.
If the backups don't fit onto a single tape then you may wish to use an
autochanger (if you aren't already!). It might be possible that you could
use a server that can stage the backups on disc before putting to tape at a
different time. You may also want to consider separating database backups
from other types of backups to speed up the time needed to recover.
You may want to remove the sample databases like northwind and pubs from
your live systems, they are re-creatable from the scripts which are
downloadable if necessary.
If databases are not updated or not updated in an ad-hoc way, then you may
wish to make them read-only and use a different backup cycle for them.
John
"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126799621.306011.34090@.o13g2000cwo.googlegro ups.com...
>I use SQLsafe to backup and compress my data, which in turn is saved to
> my backup server. My tape is HP's Surestore 6/6000 library. I have
> 5540GB tapes in the library.
> Monday through Saturday I perform a differential backup and a full
> backup on Sunday. My plan is to keep a 1 month worth of data on the
> shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
> Once the full backup is a month old, I move it to the library and
> delete the files from the disk once the backup is successful. I wont
> backup the diffs to tape and will delete the prior weeks diff backups
> once the full backup is complete.
> This is my current plan, it will change as I get useful input and see
> how it works in practice.
> My full backup of some tables are not currently large; the largest
> 5.3GB after compression. However, I tend to look forward, and forsee
> more demand on the backup server, so why backup data that hasnt
> changed? The library will be backing up 3 Linux servers, 16 windows
> servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
> maximize the storage on the Library by keeping unnecessary data off the
> library.
> Again, there may not be a practical solution to my question, but often
> I find I learn something unrelated that may help me with a future
> problem.
No comments:
Post a Comment