[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

MS SQL Server 2008 Maint. Plans

I currently have MS SQL 2008 Server running.  I want to set up some maintenance plans and set up backups to jobs.  However I can't seem to find out why I can't find New Maintenance Plan under the Management folder in Object Explorer.  I am signed in as sa and it has the dbo role.  Could somebody please help me because I have google everything I can think of and cannot for the life of me figure this out?
0
geleman
Asked:
geleman
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Paul ThompsonIT ConsultantCommented:
It sounds like you're using SQL Server 2008 Express.

The free versions don't have Maintenance plans.

http://msdn.microsoft.com/en-us/library/cc645993.aspx

If you have one of the paid-for versions of SQL Server and you have full access, you should just be able to right click on "Maintenance Plans" under "Management".

DJ
0
 
geek_vjCommented:
Kindly check if you are using SQL Server 2008 Express by executing the below query:

select serverproperty ('Edition'), serverproperty ('ProductVersion'), serverproperty ('ProductLevel')

If you get the edition as Express, then maintenace plans are not supported. So, probably you need to go for a script for backing up the databases.

If you get the edition as Standard/Developer/Enterprise, then you should be able to create the maintenance plans under SQL Management Studio --> Management --> Maintenance Plans
0
 
wolfman007Commented:
Have you installed SQL Server Integration Services (SSIS) as part of your SQL Server 2008 installation?

You need  SQL Server Integration Services to create and manage your Database Maintenance Plans.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
wolfman007Commented:
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.

from

http://msdn.microsoft.com/en-us/library/ms191002.aspx
0
 
Paul ThompsonIT ConsultantCommented:
Hi Geleman,

In addition to my first reply, SQL Server Express doesnt have an SQL Agent either, so you cant schedule tasks or jobs.

However, You can simulate scheduled backups by executing backup scripts triggered by the Windows Scheduler.


Save a Backup script like this in a .sql file:-

BACKUP DATABASE [MyDatabase]
TO  DISK = N'C:\SQLBackups\MyDatabase.bak' WITH NOFORMAT,
INIT,  
NAME = N'MyDatabase-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,  
STATS = 10
GO


Then create a .bat file to execute the script like this:-

sqlcmd -S <ServerName> -i "C:\<path to .sql file>\BackupMyDatabase.sql"


Now create a Task in the Windows Task Sheduler to execute the .bat file at the desired interval.


You could have a library of scripts to perform various backup tasks and other regular maintenance routines.

Hope this helps.
DJ
0
 
gelemanAuthor Commented:
The version I have is MS SQL Server 2008 Standard.  I am using the sa user and it is a part of the sysadmin group.  I upgraded from SQL 2005 Express and I am not sure if maybe that is what is cause in the problem.  Dr. Jacuzzi that was the route I was planning on taking but I should have the New Maintenance Plan option.  Also when I try to save the script in the backup screen to a job it says I don't have SQL Agent.  So I'm trying to figure out if there was a problem with the install.
0
 
gelemanAuthor Commented:
Not sure if this will give any help also but when we did the install we left the same instance in place and it was called SQLEXPRESS.
0
 
gelemanAuthor Commented:
I think I have just found the problem.  Standard is supposed to be in place and that is what I paid for and what I downloaded from Microsoft but I did the query that geek vj suggested and i got back:
Express Edition with Advanced Services 9.00.4053.00 SP3

now how could that have happened if I installed standard?
0
 
gelemanAuthor Commented:
I think I have just found the answer.  I was because we used that same instance.  I thought it would convert over.  So now having found that I am pretty much a db rookie and have to learn a lot hands on can anybody tell how I might be able to convert that instance over to the standard version or would i have to migrate it completely to a new instance.  I just logged into the SQLSERVER instance and wouldn't you know everything I am supposed to have is there.
0
 
geek_vjCommented:
>>can anybody tell how I might be able to convert that instance over to the standard version or would i have to migrate it completely to a new instance

You can upgrade from Express to Standard edition without any issues as it is pretty straight forward. Please find the below link about the same:

http://www.google.co.in/url?sa=t&source=web&ct=res&cd=2&ved=0CAoQFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F4%2F7%2Fa%2F47a548b9-249e-484c-abd7-29f31282b04d%2FUpgrExptoWkgStd.doc&rct=j&q=upgrade+from+express+to+standard+edition&ei=bFgwS8afKcGLkAX675T_CA&usg=AFQjCNGu1Czrnh_RaI55eoZ5hr-fSFQuVg
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now