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
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.
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.