Scheduled backup silently fails

I am running a SQL Server 2000 installation with several databases. Each
database and log is backed-up using a maintenance plan.

The scheduled maintance plan for the latest database does not run, but
displays no error. There is no entry in the job history. The same thing
happens when I try to run the individual jobs from Enterprise Manager.

I've checked the database recovery model (full), the location of the backup
files (same as the other databases), and just about everything else I can
think of. The scheduled maintenance plan for every other database runs as
it should.

What am I missing?

Thanks

Iain

re: Scheduled backup silently fails

"Iain Hosking" <iimfedupwithspamhosking@thespamfreeweather.com.au > wrote in
message news:401e53e7$0$79389$7b628d20@titanium.syd.ntt.ne t.au...[color=blue]
> I am running a SQL Server 2000 installation with several databases. Each
> database and log is backed-up using a maintenance plan.
>
> The scheduled maintance plan for the latest database does not run, but
> displays no error. There is no entry in the job history. The same thing
> happens when I try to run the individual jobs from Enterprise Manager.
>
> I've checked the database recovery model (full), the location of the[/color]
backup[color=blue]
> files (same as the other databases), and just about everything else I can
> think of. The scheduled maintenance plan for every other database runs as
> it should.
>
> What am I missing?[/color]


Is SQL Agent service running? This service is required
to be running for the above automation.

Additionally, I would disassociate the backups and the
maintenance plans by creating each as separate tasks,
the former being far more critical than the latter.





Pete Brown
Winluck P/L
Falls Creek
Australia

re: Scheduled backup silently fails

Hi,

A wild guess is you're looking at the job history via EM. You're
right clicking on the sql agent jobs and select view history. The
view history says job successful. But when you check the view step
details, you see that some of the job steps failed?? If that is the
case you'll need to query the system tables. A script to do that is
located at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30388.

re: Scheduled backup silently fails

Mountain Man

Thanks for the answer. Yes, SQL Server Agent is running. Every other
database is being backed up according to the plan.

I was hoping there was just one thing I missed with this database, but as I
say, I've checked the obvious (data recovery model, backup file names).

Do you advocate setting up a manual backup plan rather than using
Microsoft's Maintenance Plan wizard? The attraction of the wizard was that
it timestamped the files, and was basically quick to set up (there are 13
databases so far). I attended a 1-week DBA course and this seemed to be the
approved procedure. It's worked fine up to this point.

Cheers

Iain


"mountain man" <hobbit@southern_seaweed.com.op> wrote in message
news:iGyTb.40024$Wa.4755@news-server.bigpond.net.au...[color=blue]
> "Iain Hosking" <iimfedupwithspamhosking@thespamfreeweather.com.au > wrote[/color]
in[color=blue]
> message news:401e53e7$0$79389$7b628d20@titanium.syd.ntt.ne t.au...[color=green]
> > I am running a SQL Server 2000 installation with several databases. Each
> > database and log is backed-up using a maintenance plan.
> >
> > The scheduled maintance plan for the latest database does not run, but
> > displays no error. There is no entry in the job history. The same thing
> > happens when I try to run the individual jobs from Enterprise Manager.
> >
> > I've checked the database recovery model (full), the location of the[/color]
> backup[color=green]
> > files (same as the other databases), and just about everything else I[/color][/color]
can[color=blue][color=green]
> > think of. The scheduled maintenance plan for every other database runs[/color][/color]
as[color=blue][color=green]
> > it should.
> >
> > What am I missing?[/color]
>
>
> Is SQL Agent service running? This service is required
> to be running for the above automation.
>
> Additionally, I would disassociate the backups and the
> maintenance plans by creating each as separate tasks,
> the former being far more critical than the latter.
>
>
>
>
>
> Pete Brown
> Winluck P/L
> Falls Creek
> Australia
>
>
>
>
>
>
>
>[/color]

re: Scheduled backup silently fails

Iain Hosking (iimfedupwithspamhosking@thespamfreeweather.com.au ) writes:[color=blue]
> I am running a SQL Server 2000 installation with several databases. Each
> database and log is backed-up using a maintenance plan.
>
> The scheduled maintance plan for the latest database does not run, but
> displays no error. There is no entry in the job history. The same thing
> happens when I try to run the individual jobs from Enterprise Manager.
>
> I've checked the database recovery model (full), the location of the
> backup files (same as the other databases), and just about everything
> else I can think of. The scheduled maintenance plan for every other
> database runs as it should.[/color]

Well, these may be really stupid suggestions, but check that the job
is enabled, and that the schedule is enabled.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Scheduled backup silently fails

"Iain Hosking" <iimfedupwithspamhosking@thespamfreeweather.com.au > wrote in
message news:401ed1e6$0$79387$7b628d20@titanium.syd.ntt.ne t.au...

....[trim]...
[color=blue]
> Do you advocate setting up a manual backup plan rather than using
> Microsoft's Maintenance Plan wizard?[/color]

Generally yes, for a number of reasons:

1) Although the maintenance tasks can be very important
the backup is super-critical and should have IMO a dedicated
separate task in the production task queue.

2) Generally the schedules for maintenance and backups
are different cycles and thus they need to separately scheduled.

OTOH the maintenance wizard is fine for most production
sites in their early phases of operations ---- prior to any
full automation. (ie: large task queue)


Good luck,




Pete Brown
Falls Creek
Australia







[color=blue]
> "mountain man" <hobbit@southern_seaweed.com.op> wrote in message
> news:iGyTb.40024$Wa.4755@news-server.bigpond.net.au...[color=green]
> > "Iain Hosking" <iimfedupwithspamhosking@thespamfreeweather.com.au > wrote[/color]
> in[color=green]
> > message news:401e53e7$0$79389$7b628d20@titanium.syd.ntt.ne t.au...[color=darkred]
> > > I am running a SQL Server 2000 installation with several databases.[/color][/color][/color]
Each[color=blue][color=green][color=darkred]
> > > database and log is backed-up using a maintenance plan.
> > >
> > > The scheduled maintance plan for the latest database does not run, but
> > > displays no error. There is no entry in the job history. The same[/color][/color][/color]
thing[color=blue][color=green][color=darkred]
> > > happens when I try to run the individual jobs from Enterprise Manager.
> > >
> > > I've checked the database recovery model (full), the location of the[/color]
> > backup[color=darkred]
> > > files (same as the other databases), and just about everything else I[/color][/color]
> can[color=green][color=darkred]
> > > think of. The scheduled maintenance plan for every other database[/color][/color][/color]
runs[color=blue]
> as[color=green][color=darkred]
> > > it should.
> > >
> > > What am I missing?[/color]
> >
> >
> > Is SQL Agent service running? This service is required
> > to be running for the above automation.
> >
> > Additionally, I would disassociate the backups and the
> > maintenance plans by creating each as separate tasks,
> > the former being far more critical than the latter.
> >
> >
> >
> >
> >
> > Pete Brown
> > Winluck P/L
> > Falls Creek
> > Australia
> >
> >
> >
> >
> >
> >
> >
> >[/color]
>
>[/color]

re: Scheduled backup silently fails

What user is SQL Server running under?
What user is SQL Agent running under?
Who is the owner of the database?
Are all of the above the same?
If not, check the other databases for this information and change this
one to match.
If so, does the user have permissions at the location of the backup
files?


On Tue, 3 Feb 2004 00:42:59 +1100, "Iain Hosking"
<iimfedupwithspamhosking@thespamfreeweather.com.au > wrote:
[color=blue]
>I am running a SQL Server 2000 installation with several databases. Each
>database and log is backed-up using a maintenance plan.
>
>The scheduled maintance plan for the latest database does not run, but
>displays no error. There is no entry in the job history. The same thing
>happens when I try to run the individual jobs from Enterprise Manager.
>
>I've checked the database recovery model (full), the location of the backup
>files (same as the other databases), and just about everything else I can
>think of. The scheduled maintenance plan for every other database runs as
>it should.
>
>What am I missing?
>
>Thanks
>
>Iain
>
>[/color]