473,233 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

Dont backup if Database hasnt changed

I do weekly full backups of my SQL databases via a scheduled T-SQL job.
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 Camarda

Sep 15 '05 #1
3 1898
I 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

Sep 15 '05 #2
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.

Sep 15 '05 #3
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" <rc******@cablespeed.com> wrote in message
news:11*********************@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.

Sep 15 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Colin Finck | last post by:
Hello! I need to backup a MySQL database (MySQL 4.0). But it is on a shared-hosting web server and so I don't have direct server access. I also have no phpMyAdmin installed. How can I backup the...
3
by: Stanley Sinclair | last post by:
For years I have been hoping for a native db2 solution which compresses data on BACKUP DATABASE. (Using Windows.) I have heard that it will happen, but has anyone enough chutzpa to say if it will...
1
by: MeganF via AccessMonster.com | last post by:
Does it make sense to create an option to create a backup database. The folks I'm working with aren't set up really well in their office for automation. I'm helping by automating their invoice...
4
by: Andy Davis | last post by:
Is it possible to create a button with code to carry out the a weekly compact and repair of database and backup? My users know how to do this from the menu but would really like some kind process...
4
by: fstenoughsnoopy | last post by:
I have a database on a server, not split into FE/BE and it has the user level security set up. I do have one problem. I need to back up the database. According to Microsoft and other books and...
3
by: ineedahelp | last post by:
Does anyone have an efficient way to MOVE tables to a backup database using code? Thanks in advance for any help!!
1
by: bwmiller16 | last post by:
Folks - DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08022" with level identifier "03030106". Informational tokens are "DB2 v8.1.2.88", "s050422", "MI00117", and...
2
japuentem
by: japuentem | last post by:
I want to backup a database with the SQL Query Analizer with the following instruction: BACKUP DATABASE my_database TO DISK 'C:\backups\my_database.bak' WITH FORMAT GO and send me this error...
2
by: billalquist | last post by:
Here is what I am trying to do....I am using Access 2007. I created a form (frmEmailBudget) with a button (btnCreateBudgetBackup). This button needs to generate a backup of the active database...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.