Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

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/

share|improve this answer
    
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. –  Stephen Jennings Dec 20 '09 at 15:18
    
My god how much more simple it gets with this! After all, backups should be simple from the beginning... :) –  Mattias Åslund Oct 27 '12 at 7:09

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.

share|improve this answer
    
I ran the maintenance plans a half dozen times and not a single log in that folder had been touched. They were current logs, they just weren't getting hit by the plan. –  Stephen Jennings Dec 20 '09 at 6:07
    
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. –  Jason Cumberland Dec 21 '09 at 16:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.