SQL Server Maintenance Plans

You have to enable sql agent to run scheduled task. make sure that sql agent is running.

As you are saying that if you try to execute it manually even then it didn't did any thing. it mean that sql data backup plan is not created properly.

see this link to create backup plan

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

for more info with screen shot
http://www.techrepublic.com/blog/datacenter/create-database-maintenance-plans-in-sql-server-2005-using-ssis/248

The SQL Agent is up running as a local user SQLBackup and this user is a member of the SQLServerSQLAgentUsers group - does this group not give the user the required privileges to undertake the SQL Agent tasks?

As for creating the plan, I used the wizard and it appears okay!

In Sql server agent when you see job history for this particular plan what it is showing?

The Activity Monitor claims never to have run the plans.

not in activity monitor.
If you open Sql Server Agent, Inside you will see Jobs Tab. In jobs tab you will find job that was created by Maintenance plan. If you right click on that job and clik on View history. What it is showing?

There is nothing there. However, in the Error Logs for Server Agent there is this:

05/19/2011 13:11:49,,Error,[382] Logon to server 'APPLE' failed (ConnLogJobHistory)
05/19/2011 13:11:49,,Error,[298] SQLServer Error: 5<c/> A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
05/19/2011 13:11:49,,Error,[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
05/19/2011 13:11:49,,Error,[298] SQLServer Error: 5<c/> Named Pipes Provider: Could not open a connection to SQL Server [5]. [SQLSTATE 08001]

Can you create maintenance plan again? Because if you have created a maintenance plane for backing up database, then corresponding job should also get created in Jobs tab under SQL Server Agent.

Sorry, to clarify - the job is there but there is nothing in the log file summary.

now right clik on job and execute it. it will tell exact error message

It's now doing what it did before and sitting on Status "In Progress" The plan I'm running backs up a couple of very very small databases so it shouldn't take long at all.

wait for this to execute it may or may not execute. then you can see the log for job. if it is taking time to execute this then it means it is not able to make connection to database.  wait for some time to execute it

No error message has come up. It's just sitting saying in progress. Could the error message in the logs have anything to do with it? I don't know why it's trying to connect to APPLE is surely should be connecting to local no?

It should connect to local database only. I created database backup plan on my local system and it worked fine. i am still wonderring why it is not working on ur system.

I left the process running over night and got the following error:


===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con)
   at Microsoft.SqlServer.Management.Smo.PostProcessJobActivity.GetColumnData(String name, Object data, DataProvider dp)
   at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
   at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(StringCollection query, Object con, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.get_LastRunDate()
   at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.WaitForJobToFinishAction.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

A severe error occurred on the current command.  The results, if any, should be discarded. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
Server Name: (local)
Error Number: 0
Severity: 11
State: 0


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

are u able to take database backup by right clicking on database?

Yes - this executes perfectly.

That lets me run it if I press execute under jobs but the scheduled task doesnt run and I get the Logon to Server Local error again in the Server Agent logs. Could this be because server agent runs as a domain user (eusasys1@domain.com) I have added domain\eusasys1 to the SQL server logins but it wont let me add eusasys1@domain.com as it's not a valid NT account name or something.

Victory! I got the SQL Agent to run as Domain\eusasys1 and this appears to allow the Job Scripts you suggested run!

Thanks buddy!