Scheduled job for a backup device stopped working

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 a
nd
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, the
n
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, NOU
NLOAD ,
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 SagerSounds like SQL Agent is stopped. Have you checked that?
Andrew J. Kelly SQL MVP
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> 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, N
OUNLOAD
> ,
> 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
>|||Check to make sure the SQL Server agent is running
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Rog" <Rog@.discussions.microsoft.com> wrote in message
news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
> 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, N
OUNLOAD
> ,
> 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
>|||No.. the SQL Server Agent is running, plus for this scheduled jobs, there is
no end-date.
Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Thanks Kevin...but I check the SQL Server Agent and it is definitely started
and running. Also for these jobs, there is no end-date. I have taken
several screen shots, but don't know how to upload them.
Cheers...Roger
"Kevin3NF" wrote:

> Check to make sure the SQL Server agent is running
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Rog" <Rog@.discussions.microsoft.com> wrote in message
> news:2421FB88-6004-4490-8B66-6F93A2E6CA7D@.microsoft.com...
>
>|||Update:
Under Enterprise Mgr> Management > Sql Server Agent > Jobs...
.
I can see the last time all these "FULL Backup / Sunday only" jobs run (ie
Dec 11th) and the next run show 8th of Jan. So it will be interesting to see
on Monday morning if these have started again?
Cheers...Roger