Scheduled job for a backup device stopped

Hi all...
The are a number of real databases on our SQL Server 2000 and initially I
set out a number of Backup Devices to execute both FULL and DIFFERENTIAL
backups. My concern is with the FULL backups and the DIFFs are working just
fine. I scheduled a number of FULL backup jobs to execute each Sunday only
and at difference times, such as, the 1st database at 4 am, the next at 4:15
am, the next at 4:30 am etc…each Sunday only. Well I was monitoring them and
like clock work each of these real databases were over written as defined.
The job history shows that a FULL backup for these jobs was performed Nov
13th (when initially defined); the next showed the jobs run at Nov 20th, then
Nov 27th, then Dec 4th, then and Dec 11th. Each of .bak files had the
correct date stamp, but all jobs stopped? I expected, after returning from
holidays to see in the job history a backup for Dec 18, Dec 25, and Jan 1st.
But no, the last run was the 11th of Dec?
The definitions of one particular job (under the Management> SQL Server
Agent > Jobs definition) is as follows:
Name : Auscott Sunday overwrite BU
Under its property definition:
General Tab> enable is checked, Category is Database Maintenance, Owner is sa
Steps Tab> there are 2 Steps with the first having a Type: Transact-SQL
Script (TSQL), and Database: Auscott, and Command:
BACKUP DATABASE [Auscott] TO [Auscott_full_backup] WITH INIT, NOUNLOAD ,
NAME = N'Auscott Sunday overwrite BU', NOSKIP , STATS = 10, DESCRIPTION =
N'Sunday scheduled overwrite Backup of AUSCOTT DB', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='Auscott'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='Auscott')
RESTORE VERIFYONLY FROM [Auscott_full_backup] WITH FILE = @.
Schedules Tab> is defined as Schedules Sunday AUSCOTT job, enabled,
Recurring radio button with a description of “occurs every 1 week(s) on
Sunday, at 4:15:00 am
To my way of thinking, every thing seemed to be defined correctly using
backup devices and it wrote 5 times, every Sunday, at 4:15am but stopped?
And there was no disck space issues...so I am stumped.
Does anyone have any suggestions?
Cheers….Roger Sager
PS - We are on SQL Server 2000 SP4
Sorry folks...this is a duplicate entry. Please see the other one with many
postings.
Roger Sager
|||Hi ,
Nice job but u can set a database maintinance plan which will delete ur
0ne week old backup files.
If u want to keep those backups then write a job to copy them to other
location on network and delete from the original server so sever will
not run out of disk space.
By the way on which system u are doing backup is't ur production server
if yes then u do ur back on the network derive
So in case if something happens ur still safe.
From
Doller