Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server.

Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy either, as it would be if there were legacy-mode databases on the server. I am certain this is SQL Server 2008 R2 and not Express. I have checked the version and verified the splash screen when studio starts are not Express. I am logging in as the SQL account sa, and the account does have the sysadmin server role.

Lastly, right clicking on a database in SSMS and navigating to "Tasks" does not show any Maintenance Plan option.

Clearly, I'm missing something, and Google is coming up empty considering the scenario I've outlined. Any ideas? Thanks.

EDIT: select @@version returns "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) Apr 22 2011 11:57:00 Copyright (c) Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)"

share|improve this question
3  
So, it says it is the Express Edition with Advanced Services. I believe it doesn't come with SQL Server agent,or the ability to run maintenance plans or jobs. –  Peter Schofield Jul 19 '11 at 14:18
1  
Yes, thank you. I pasted the edit in, saw an email from a colleague, and realized the answer was right in the version. I'm a doof. –  Tohuw Jul 19 '11 at 14:25
add comment

2 Answers

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent. Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.

share|improve this answer
add comment

Run and check the following:

USE MSDB
SELECT * FROM sysmaintplan_subplans
SELECT * FROM sysmaintplan_plans

If "sysmaintplan_plans" is empty check if the folderid for the maintenance folder is 08AA12D5-8F98-4DAB-A4FC-980B150A5DC8

select * from sysssispackagefolders where foldername ='Maintenance Plans'

If it is not the above id update the folderid like this and new maintenance plans will be shown correct.

update sysssispackagefolders
set folderid = '08AA12D5-8F98-4DAB-A4FC-980B150A5DC8'
where foldername ='Maintenance Plans'

It worked for me with an SQL Server 2008 R2

Cheers Peter

share|improve this answer
add comment

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.