Adding SQL Server Agent to SQL Server 2008 R2 SP1

Question

Post reply

Adding SQL Server Agent to SQL Server 2008 R2 SP1

sjmadden

Old Hand

Points: 343
More actions
February 7, 2012 at 7:45 am

#256367

Hi,
I am wondering if someone can confirm my suspicions please?!
I recently inherited a SQL Server 200 R2 SP1 server (and many other dbs besides but that's another story :crazy:) .
It looks to me like SQL Agent hasn't been installed (can't find it anywhere in SSMS) and I now need to schedule backups.
I tried to re-run install to "add a feature" but SQL Agent didn't seem to be an option.
Is it the case that you cannot add SQL agent without reinstalling the whole server?
Is there anyway I can achieve this without taking down the instance??
Really appreciate any comments including any opinions the pros, cons and alternative to using SQL Agent for backup scheduling.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Is it SQL Express you have installed? SQL Agent is not part of SQL Express.

On other versions SQL Agent should show up in the object explorer when connected to the server.

Although it can be disabled, it should still show up.

Cheers

thecodeguys Ten Centuries Points: 1076 More actions · Answer 2

As far as pros and cons: you should have very little problems with SQL Agent as far as handling your jobs. As far as backing up your databases you can use Maintenance plans or scripts. I have read on the forums about people having minor issues with SQL Agent, but I have rarely had any issues with it.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Can you run

select @@version and paste the results so we can confirm the SQL version you are running?

Shamless self promotion - read my blog http://sirsql.net

sjmadden Old Hand Points: 343 More actions · Answer 4

Thanks all for the responses.

I believe it is EE but I await correction:

select @@version

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

If you can't find it in SSMS, it's probably because you don't have enough permissions on SQL Server. If you log on to the server and run SQL Server Configuration Manager, you should be able to see the SQL Server Agent service in there.

John

SQLback SSCrazy Points: 2897 More actions · Answer 6

I agree with John. This looks like a permission issue. To see SQL Agent you need to be a member of sysadmin role or a member of one of the following three roles in msdb database

SQLAgentUserRole

SQLAgentReaderRole

SQLAgentOperatorRole

Make sure SQL Agent service is started in SQL Server Configuration Manager.

sjmadden Old Hand Points: 343 More actions · Answer 7

Thank you, thank you, thank you all!!

To my eternal embarrassment this was the problem. I was TOLD the account I was using had access but I didn't actually check. Your replies have saved me a serious red face in the office though.