DATABASE Maintenance Plans

[joeythelips]

Hi,

i have a database maintenance plan which i cannot get to work.

I back up master, model, msdb and one user database as part of the plan.
I have none of the optimisations or integrity options checked.
I back up the data files and transaction logs at the same time.

However, the plan fails every time.

sql server agent is running.
there is plenty of disk space.
there are no entries in the sql server logs.

has anyone any ideas?

 

[JayKusch]

The maintenance plan you create to back up the 3 system DBs and a User DB has also created a job.

Drill down in Enterprise Manager to Management..SQL Server Agent..Jobs.

You should see a job that references your Maint Plan. Right-click on the job and choose "View job history". Then check the box labled "Show step details".

In there we may find some clues as to why your job is failing.

My initial guess is that you are trying to back up the transaction log for the Master and MSDB databases which cannot be backed up due to the databases being set to Simple mode.

I would suggest forgoing the maintenance plans in creating backup jobs and create your own backup jobs in the jobs area that you are veiwing the history on.

I would seperate the system and user DB backups to have thier own jobs.

Thanks

J. Kusch

 

[joeythelips]

Hi,

thanks for your help.

i checked the job history and got the following:

The job failed. The owner () of job DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' does not have server access.

How do i check who the job runs as? I take it it is run by the user who my sql server registration was set up by?

 

[joeythelips]

also, the recovery mode is simple!

 

[JayKusch]

you could set it up as "sa" for the time being until a valid account is determined or created.

For the Master and MSDB, do not create TLog backups for these DBs.

Once again, try to get away from using maintenance plans.

Thanks

J. Kusch

 

[joeythelips]

hi.

i have the job setup now to be run as sa.

on another note, what are the implications/advantages of having full recovery mode versus simple?

 

[JayKusch]

In full recovery, you are enabling the use of the transaction log so that in the case of a failure, you could restore your last full backup, then apply all of the transaction log backups you have snapped to get you pretty close if not right up to the point of failure if the proper disaster recovery plan is implemented.

Simple mode pretty much turns off "most" logging to the TLog so that it does not grow so much. You are then only able to recover from a disaster up to the last full backup you performed. once again, based on how you perform your backups as fulls only or as a combination of fulls and differentials.

Thanks

J. Kusch