Solved

Cant shedule backup on SQL 2005 Workgroup. "SQL Agent is not supported"

Posted on 2009-05-04
18
1,103 Views
Last Modified: 2012-05-06
I need to set up an automated backup procedure for my database, but when trying to "Scrypt action to Job" , I get the error "SQL Agent is not supported on this edition of SQL Server."

I know this is normal for express edition, but we are running the workgroup edition that came with sbs 2003 premium. So far I have not been able to find any documentation to confirm or deny that the error is in fact correct.

Is SQL agent really not supported by 2005 Workgroup edition ? The reason I am doubtfull is that there has been allot of software removal and modification on this server since it was initially set up in 2004, and this MIGHT be some kind of version conflict, or am I mistaken ?

How can I shedule a SQL backup without a intamete knowlige of SQL ?
0
Comment
Question by:Xorb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
18 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 80 total points
ID: 24293671
SQL Agent is available for workgroup. have a look at

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 80 total points
ID: 24293677
is SQL Agent services is running on your server?
0
 
LVL 2

Author Comment

by:Xorb
ID: 24293877
Hi RiteshShah
I think you've hit the nail on the head .. a problem with the services not running. Events in event log show things like :

The file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

The file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

The SQL Server (MSSQLSERVER) service terminated with service-specific error 1814 (0x716).

The SQL Server Agent (MSSQLSERVER) service depends on the SQL Server (MSSQLSERVER) service which failed to start because of the following error: The service has returned a service-specific error code.

I'm looking into it now
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24293900
:) have a look and let me know if you need any help.
0
 
LVL 2

Author Comment

by:Xorb
ID: 24293972
Actually I do. When it comes to MS SQL, I dont have much experiance. I can find the files in question in manigement studio, but where is the compressoption that I need to untick ? I'll keep looking in the meantime
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 80 total points
ID: 24293986
first of all try to start MSSQLServer and SQL Server AGENT job from control panel->administrative tools->Services
0
 
LVL 2

Author Comment

by:Xorb
ID: 24294037
Oh I see what went wrong ! Windows compresses a file during disk cleanup if it has not been accessed in a long time. We have not been using the SQL for a while, so the files were NTFS compressed (Show as blue in explorer)

Problem Solved !!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24294049
:) good to know that.
0
 
LVL 2

Author Comment

by:Xorb
ID: 24294083
OR NOT
Now the services are running fine, the agent is running fine (since I decompressed the files) everything seems ok, but I still get :

TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to script action to SQL Server Agent job.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

SQL Agent is not supported on this edition of SQL Server. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.UnsupportedFeatureException&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Any advice ?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24294111
have you updated your server with latest SQL Server service pack?
0
 
LVL 2

Author Comment

by:Xorb
ID: 24294237
SP2 is installed. As far as I can tell there is a sp3, but that is for Vista. Is sp2 the correct version ?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 45 total points
ID: 24301406
>>The reason I am doubtfull is that there has been allot of software removal and modification on this server since it was initially set up in 2004, and this MIGHT be some kind of version conflict, or am I mistaken ? <<
Why not make sure:

Select SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel')
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24481280
nothing got clear so far as Author didn't came back so now its up to you A3
0
 
LVL 2

Author Comment

by:Xorb
ID: 24502525
Hi Everyone
I am so sorry that I left this question "hanging" I am in fact trying to shedule down time for the server so I can install SP3 for SQL, but currently the client keeps requesting the donwtime be done "later"

The question is open, I am keen to try the solutions and give feed back. You'r patience is appreciated.

Thanks
0
 
LVL 2

Author Comment

by:Xorb
ID: 24772766
First off, sorry I took so long to give feedback. I have not been able to shedule the downtime, but I have continued my investigation, and came accross the following.

Even though the server is a 2003 sbs premium box, and I KNOW i installed the SQL work group edition, now when I use " Select SERVERPROPERTY('Edition'), SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel')"
I get " 9.00.3077.00 | sp2 | Express Edition " 

How do I address this issue ? Why is my data running in express edition if I'm supposed to have work group ? Am I right in saying that is why the shedule function on backups in SQL management wont work ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24772789
could it be that you have several sql server instances running?

the output of SERVERPROPERTY is not lying for sure...
0
 
LVL 2

Author Comment

by:Xorb
ID: 24791893
There is most definitely more than one instance of SQL running. The 3rd party accounting software was installed by a support agent of the aforementioned software.
 
So Ill have to find a way to migrate the data from one instance to another in order to schedule backups.

Thanks for everyones help.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question