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"Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote in
message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> 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?

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|||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.|||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...
> "Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote
in
> message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> > 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?
>
> 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
>
>
>|||Iain Hosking (iimfedupwithspamhosking@.thespamfreeweather.com.au ) writes:
> 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.

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/techin.../2000/books.asp|||"Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote in
message news:401ed1e6$0$79387$7b628d20@.titanium.syd.ntt.ne t.au...

...[trim]...

> Do you advocate setting up a manual backup plan rather than using
> Microsoft's Maintenance Plan wizard?

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

> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
> news:iGyTb.40024$Wa.4755@.news-server.bigpond.net.au...
> > "Iain Hosking" <iimfedupwithspamhosking@.thespamfreeweather.com.au> wrote
> in
> > message news:401e53e7$0$79389$7b628d20@.titanium.syd.ntt.ne t.au...
> > > 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?
> > 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|||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:

>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