June 19, 2006 at 7:05 pm
Hi all
I have inherited an old Cognos system which generated regular backup sets to a single disk based device. Currently, the backup file is 14Gigs for a 400 meg database. I get the feeling I may be able to do away with some old backup sets. I am unable to change the backup statement as it is embedded within Contributor. Therefore I am unable to use the expirydate or retention days clauses.
Is there a way to script a command to remove certain backup sets from a device?
This is on SQL 2000/sp4
Thanks everyone
James
June 22, 2006 at 8:00 am
This was removed by the editor as SPAM
June 23, 2006 at 1:57 pm
Hi James -
This is Cognos Planning, isn't it?
I recall the installation team/developers installing and leaving, then me checking disk about 3 months later and seeing the media set was consuming most of the disk. No Media Retention was set so everything was ganged up on the Media Set. Nice default. It was really was an overly aggressive backup strategy for the limited development that was going on.
Sorry I'm vague about what it was I found but it was either a SQL resident Database Maintenance Plan, or a job executing Stored procedure or OSQL or an external OSQL script being executed from Contributor itself? Something I had not setup for sure.
I think it was the OSQL script, and I either tweaked it with a more appropriate Media Retention setting, or just disabled the SQL job that executed it. I found some way to control it. Still, I recall I had to DELETE the existing Media set and start over for some reason, so I did my own intermediate self-made backups to run for a week to cover before I did it.
Sorry I can't be more specific but I have no way to reference what I just said as they have since ditched the system. But that's the way I remember it.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
July 17, 2006 at 10:10 am
I've basically got the same problem, only it doesn't have anything to do with Cognos. It's just on a production MSSQL 7 database I'm DBAing.
I'm new to using MSSQL backup strategies and all I want is something very simple:
A way to use a single disk backup device which rotates backups out of it every 2 - 3 weeks or so.
I've tried to do this using INIT, FORMAT, RETAINDAYS, etc., to no avail. Everything that I'm reading about it seems to be setup for multiple devices or using tapes or something else. What I had in mind is that I could have, e.g., three full backups on one device, each with RETAINDAYS of 14, e.g. When the third one went to write, it'd see that the first backup set was expired, so it'd overwrite *just that one*, not the entire device and proceed. I'm getting the feeling that this will not work in the MSSQL backup scheme.
Am I correct? Does anyone have a reasonable suggestion for how I might accomplish my plan?
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply