The best part of learning is by the error messages that each one of you send me over the years. These are a great source of experimentation and a lot of searching to get to the appropriate error resolution. I take this an opportunity to connect with various people and look for active solutions one has achieved from their experience. One of my readers sent an email as shown below about Maintenance Plan Folder Missing Under Management in SQL Server Management Studio:
Hi Pinal,
I have installed SQL Server 2016 for my 3rd party software. Since it’s a financial data, I wanted to make sure that we have backups taken regularly. I read on internet and found that I can created maintenance plan to take backup on scheduled basis. I was surprised to see that I am not having folder called Maintenance Plan. I have tried to repair of management studio and SQL Serve engine, but none of them helped.
Do you have any pointers to fix this weird issue? As you must have imagined that I am not a SQL DBA but a DBA by project demand, so please be generous.
Thanks in advance,
<One of blog Reader>
In my reply to his email I asked for complete snapshot of object explorer and output of select @@version output. Since it was SQL Server 2016, I thought that there is something new. While I was waiting, I tried to think the cause and created a public account in my lab’s SQL Server. But, there is what I saw when I logged it with that account, which was not a Sysadmin.
Yay!! I don’t see SSMS but this is not exactly matching with reader’s SSMS. When I got his reply, things were crystal clear. Here is the output he shared with me for “select @@version”
Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)
This was an EXPRESS edition of SQL Server. Since there is no SQL Agent with SQL Server, there is no point of scheduling and hence no maintenance plan folder.
To summarize, there could be 2 reasons which I could find.
- Logged in account not having sysadmin permissions. Below query would return 0.
SELECT IS_SRVROLEMEMBER('sysadmin')- Express edition of SQL Server. Below query would say express edition
SELECT SERVERPROPERTY('Edition')Do you know any other reason? Please share via comments. I am sure this will be a learning for me too about SQL Server Management Studio.
Reference: Pinal Dave (https://blog.sqlauthority.com)
25 Comments. Leave new
Even same problem i am facing please give me the solution ……
SELECT IS_SRVROLEMEMBER(‘sysadmin’)……………… This query return 1 in my sql server.
SELECT SERVERPROPERTY(‘Edition’)……….. This query return Express Edition (64-bit)…… but still under management folder there is maintenance plan folder is missing….. please give me solution…..
I AM ALSO HAVING SAME ISSUE
SELECT IS_SRVROLEMEMBER(‘sysadmin’)……………… This query return 1 in my sql server.
SELECT SERVERPROPERTY(‘Edition’)……….. This query return Express Edition (64-bit)…… but still under management folder there is maintenance plan folder is missing….. please give me solution…..
so if it does not have maintenance plan …..this means we can never automate backups on it?
Indeed thats one of the drawbaks from using the free database server.
True. But automation can still be done but not via SQL Agent.
Thanks for article. Migrated databae for got to give my admin account sysadmin rights.
Thanks for sharing it Gary.
Does the Standard Edition is supposed to have BAckup Plan ?
Of course my friend. All Editions of SQL Server supports backup.
PLEASE GIVE ME SOLUTION I AM ALSO HAVING SAME ISSUE
SELECT IS_SRVROLEMEMBER(‘sysadmin’)……………… This query return 1 in my sql server.
1, Express Edition (the free version of SQL Server) doesn’t have maintenance plans because it has no SQL Server Agent, which is the constantly-running-scheduler that kicks off jobs like maintenance plans. Below query would say express edition: SELECT SERVERPROPERTY(‘Edition’)
2, THE STANDARD EDITION, To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role. You can find more information on this subject at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191002(v=sql.105)
In addition, Logged in account not having sysadmin permissions. Below query would return 0.
SELECT IS_SRVROLEMEMBER(‘sysadmin’)
I am an SA (first query returns 1) and i have standard edition but i still cannot see the running maintenance plan. help!
I AM ALSO HAVING SAME ISSUE
SELECT IS_SRVROLEMEMBER(‘sysadmin’)……………… This query return 1 in my sql server.
SELECT SERVERPROPERTY(‘Edition’)……….. This query return Express Edition (64-bit)…… but still under management folder there is maintenance plan folder is missing….. please give me solution…..
Please help…
Running SQL Server 2017 Standard Edition as sysadmin has the same issue. At least if SQL Server is running in docker (linux).
Hello Pinal,
Does SQL Server 2017 developer edition has Maintenance plan for Linux OS?
Does Microsoft SQL Server 2017 Developer Edition (64-bit) on Linux (Red Hat Enterprise Linux Server 7.8 (Maipo)) has Maintenance plan?
Maintenance Plans is not support in Linux version.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes-2019?view=sql-server-ver15
Upgraded from 2014 express edition to 2016 standard, and maintenance plan folder still not showing. I kept the instance name the same (MSSQLEXPRESS2014) would this cause any issues.
Installed SQL Server 2019 Standard Edition on server 2019. Not seeing the Maintenance folder under management either. Any resolution to this issue known?
Thank you in advance.
same issue i am facing installed SQL std 2019 but not able to see even with sysadmin..please help
SELECT IS_SRVROLEMEMBER(‘sysadmin’) esta consulta me devuelve un 1 y esta SELECT SERVERPROPERTY(‘Edition’) me devuelve edicion estandar 64 bits pero con el usuario sa no me deja hacer planes de mtto, me pueden ayudar por favor