Unable to back up SQL Server databases using a maintenance plan

Question

I am trying to create a maintenance plan that will run automatically and back up my SQL Server 2005 databases automatically. I create a new maintenance plan and add a "Back Up Database Task", select all databases, and choose a path to back up to. When I save and try to execute this plan, I get the following error message:

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

Execution failed. See the maintenance
plan and SQL Server Agent job history
logs for details.

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

Job 'Backup.Subplan_1' failed.
(SqlManagerUI)

Program Location:

at
Microsoft.SqlServer.Management.SqlManagerUI.MaintenancePlanMenu_Run.PerformActions()

I've checked the maintenance plan log, the agent log, and just about every log file I can find and there are no entries at all to help me figure out why this is failing.

If I right-click on a specific database and select "Back Up", the task succeeds. I tried changing the plan to back up just that one database and it still failed.

I've tried running the plan with both Windows authentication and SQL Server authentication with the sa account. I also tried specifically granting the SQL Server Agent user account full privileges on the backup folder, but it still failed.

While searching the web for clues, the only solution I've run across so far suggests running sp_configure 'allow_update', 0. I tried this but allow_update was already set to 0 and it did not fix the problem.

The Windows server and SQL Server have all updates applied to them.

Thanks for any suggestions!

Solution 1

I am NOT a great fan of maintenance plans in SQL Server and have heard of so many issues with them on the forums. If you are open to new ideas, have you looked into regular T-SQL scripts for managing the maintanence tasks instead of MP. Ola Hallengren has some excellent scripts and am currently using them in 3 of my servers and they work great. The quality of code is vouched by many SQL Server MVPs too. I know this offtopic on what you asked but wanted to throw in another option. You can find the scripts @ http://ola.hallengren.com/

Solution 2

The log that a maintenance plan generates defaults to c:\program files\microsoft sql server\mssql\mssql.1\log (I think, this is relative to where you installed the instance) and this log file should get you closer to the problem. A sure way to figure it out is to find the location of the master database files and up one directory from that there should be a LOG folder.

One thing that could be a problem is that maintenance plans in sql 2005 and above are actually integrated services packages and might need SQL Server Integration Services installed on the server. Some builds of SQL 2005 and all versions of SQL 2008 require this for maintenance plans to run.

Comments

[Stephen Jennings]

I'm not set on maintenance plans, I'm really just looking for an easy way to backup the databases so I can archive them. I'll definitely take a look at these. Thanks.

[user2864202]

Then check out my second paragraph and see if that applies, doesn't sound like the package is running at all. Other option is to try what Sankar is suggesting and use scripts to backup the databases.

[Mattias Åslund]

My god how much more simple it gets with this! After all, backups should be simple from the beginning... :)

[Wouter]

I've changed the target directory of the log file and gave "Everybody" full access to it to be sure there are no permission issues. No file gets created... I've also tried installing the Integration Services, still the same error...