Question

How to create a maintenance plan in SQL Server Management Studio Express

SQL Server 2005, SQL Server Express Edition, Microsoft SQL Server Management Studio, MSSMSE

I’m running SQL Server 2005 Workgroup edition with SP2. I’m trying to create a new Maintenance Plan in Microsoft SQL Server Management Studio Express (MSSMSE) by right clicking on the Maintenance Plans object icon. The only option is Refresh - there’s no manual method or wizard.

I'm logging in as sa and my server role is sysadmin. I had already changed the authentication as mixed. I’ve seen lots of posts and they all say I simply need to have the sysadmin server role. But there must be something else, maybe services that need to be running?

SQL Server Express edition doesn't support maintenance plans as using the maintenance plans requires have Integration Services installed. As Express edition doesn't include Integration Services you can not create a maintenance plan. To rebuild the indexes you will need to write scripts to handle the work, then run then from a scheduler.

As the Express edition also doesn't include the SQL Agent I usually recommend using the Windows Task Scheduler to run SQL Agent jobs.