Scheduled Backups question

Scheduled Backups question

Post by Alle » Sun, 10 Dec 2000 01:26:44



If I want to back up my database each day and keep 28 days of backups around at
all times, do I have to use 28 separate dump devices?  Is it possible to use 7
devices for each day of the week and have the system automatically keep up to 4
dumps per device?

I've tried playing around with RETAINDAYS and INIT but i can't seem to achieve
what I want.  Either the dumps stay and never get overwritten or I get an error
message that says an unexpired dump cannot be overwritten.

THanks for the help.

 
 
 

Scheduled Backups question

Post by Matthew Bur » Sun, 10 Dec 2000 09:48:05


Unfortunately, SQL Server doesn't allow what you're trying to do. If I understand
you correctly, you'd like an arrangement where you kept 4 backups on each device,
such that the 5th backup would overwrite the oldest backup on the device. i.e.
when day 29 rolls around, you want it to overwrite only day 28's data, not any of
the other backups (e.g. day 8, day 15, and day 22).

As I said, SQL Server doesn't allow this. You have the option of either
overwriting all backups on the device, or appending backups to the end of the
device.

One alternative you could consider are backing up to a tape device thus allowing
you to rotate tapes into the tape drive. With such a solution, you would need
only one backup device, and could use 28 tapes. The disadvantage is the cost
incurred in purchasing the tape drive, the slower performance for the tape, and
the extra work needed to go in and rotate the tapes on a daily basis.

Another alternative to consider is using 5 backup devices (instead of 7) and
store a week's worth of data on each. You would rotate the devices thusly: Backup
to device 1 on the first week, device 2 on the second, 3 on the third, 4 on the
fourth (you now have 28 days worth of backups), backup to device 5 on the fifth,
and then start over with device 1, using the WITH INIT option on the first backup
to that device (continue the cycle thereafter). You would retain 28 days worth of
data (plus some due to the extra device). You would always use the WITH INIT
option on the first day of the week that you backed up to a given device, and
then you would exclude that option for the remaining days of that week.

Just some thoughts. Good luck.

Matthew Burr

Quote:-----Original Message-----

If I want to back up my database each day and keep 28 days of backups around at
all times, do I have to use 28 separate dump devices?  Is it possible to use 7
devices for each day of the week and have the system automatically keep up to 4
dumps per device?

I've tried playing around with RETAINDAYS and INIT but i can't seem to achieve
what I want.  Either the dumps stay and never get overwritten or I get an error
message that says an unexpired dump cannot be overwritten.

THanks for the help.
.


 
 
 

Scheduled Backups question

Post by Bart Simps » Mon, 11 Dec 2000 07:58:31


SQL7 EM. Database Maintenance Wizard.
Skip everything but Backup portion. Check box 'Remove files older than
4 weeks'.
Problems: you need space large enough to fit 28 backup files,  all
files located in the same place.

Bart Simpson



Quote:>If I want to back up my database each day and keep 28 days of backups around at
>all times, do I have to use 28 separate dump devices?  Is it possible to use 7
>devices for each day of the week and have the system automatically keep up to 4
>dumps per device?

>I've tried playing around with RETAINDAYS and INIT but i can't seem to achieve
>what I want.  Either the dumps stay and never get overwritten or I get an error
>message that says an unexpired dump cannot be overwritten.

>THanks for the help.

 
 
 

1. Scheduled recurring backup jobs getting schedule disabled after last run of day

I am administering a SQL Server 2000 installation that I have set up SQL
Server Agent jobs to backup up the transaction logs of several DBs once an
hour.  Each job has a recurring schedule set up to run between 8:00 and 5:00
each day, with no end date.  The problem I'm having is that after the last
job runs each day, the schedule is disabled.

I have 9 databases I've set this up on, and this happens every day,
requiring that I re-enable the schedule at the start of the morning.  The
odd thing is that it doesn't happen on every database.  Usually 8 of the
nine are disabled, but one is left running.

I haven't tried installing SP1 yet, but I checked the read-me notes and
didn't find anything that seemed to relate to this.  I've checked Technet
and the MS Knowledge Base, but haven't found anything that relates to this.

I also administer 2 SQL 7.0 installations that have similar types of jobs
set up.  None of those are having any problems, except for one database that
I set up using SQL 2000 enterprise manager to create the job.  That one
database exhibits the same problem.  Very strange.

If anyone has any ideas, I'd appreciate it.

Butch Weber

2. Data Access Middleware & a Business User's view of data

3. Scheduled backups not running - Newbie question

4. Oracle Web server ????

5. How to schedule 2 different jobs, a full backup and an incremental backup

6. FL - Data Modeler/Designer w/ experience with Informix.

7. Schedule Backup in MSDE

8. DBcombo click event problems

9. Scheduled backups through Enterprise Mgr.

10. Scheduling File Backups in SQL Server 2000

11. Scheduled backup of OLAP databases

12. Help! Backup schedule disable by itself