SQL Server maintenance plan fails with a very vague error message

Yossu 11 Reputation points
2022-12-12T18:03:45.8+00:00

I had been running SQL Server 2019 Developer Edition on my Win10 PC for a couple of years, and had a maintenance plan that ran every night to shrink and then backup all user databases to an external drive, and then delete backup files older than 1 week.

Last week I rebuilt my PC, and installed Win11. I set up a maintenance plan following what I had done previously. In case it helps, here is the designer that shows the main details...

269781-maintenance-plan.png

I can supply more details if needed, but the designer shows pretty much everything of use.

I set this to run every night, but none of the databases are backed up.

The event viewer shows an Error entry for SQLISPackage150 with the message "Package "Backup" failed." Doesn't give me any more information.

If I try running the job manually, I get an 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()

If I look at the message for the actual job, all it says is "_Job 'Backup.Subplan_1' failed. (SqlManagerUI)_", which doesn't help at all.

As an experiment, I copied the SQL for one of the backups...

BACKUP DATABASE [Northwind]  
TO DISK = N'E:\SQL Server backups\Northwind_backup_2022_12_12_161327_7041409.bak'  
WITH NOFORMAT, NOINIT,  
NAME = N'Northwind_backup_2022_12_12_161327_7041409', SKIP, REWIND, NOUNLOAD,  STATS = 10  

...into a new Query Analyser window, and it ran fine.

At this point I'm a bit lost. I have no idea why the job is failing, as it's not giving me any information.

Anyone able to help? Please let me know if there is any more information I can give.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,016 questions
{count} votes

6 answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-12-12T19:03:29.113+00:00

    Instead of debugging your error message from the built-in maintenance plan, I would like to introduce your another solution from Ola Hallrngren for database maintenance work.
    This is commonly used by many professional around the world.

    ola.hallengren.com

    0 comments No comments

  2. Seeya Xi-MSFT 16,471 Reputation points
    2022-12-13T02:48:02.387+00:00

    Hi @Yossu ,

    Welcome to Microsoft Q&A!
    Using Ola Hallrngren' script's is a good method that many professionals use. If you don't want to use it, then check out the related logs for more information.
    269853-image.png

    Best regards,
    Seeya


  3. Yossu 11 Reputation points
    2022-12-13T14:16:40.757+00:00

    Thanks to both of you for the suggestion, I'll take a look at that.

    However, I'd still like to know what's going wrong with my maintenance task. It bothers me that something so basic can just fail, and not give any useful error message. Surely there must be some way of finding out what went wrong.

    0 comments No comments

  4. Yossu 11 Reputation points
    2022-12-13T14:22:18.147+00:00

    Hmm, just looked in the server configuration manager, and can see that the SQL Server Agent service is running under SQLSERVERAGENT (the default). However, the backup folder does not have that user in its permissions list. I tried adding it, but the user doesn't exist.

    I don't know if this is the issue, as I set the permissions for Everyone to "Full control" as a test, and the job still failed, but thought I'd mention it in case it is relevant.


  5. Yossu 11 Reputation points
    2022-12-19T20:56:19.59+00:00

    Posting this as a top-level post rather than a reply to someone else so it's more visible.

    I just tried creating a new backup maintenance plan, using exactly the same steps and parameters as before, and it worked! I right-clicked and choose Execute, and all my user databases were backed up as expected.

    I've scheduled it to run every night as before, so will have to wait until tomorrow to be sure it's worked, but the previous attempt never run even on demand, so I'm hopeful this will be the answer.

    When in doubt, delete it and try again!

    Thanks again to all those who gave advice.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.