SQL Server Agent not working in SQL Server 2014 Express

Question

I have a restaurant with two computers, on the same network, each computer has SQL Server 2014 and my POS Software (Point of Sale).

Every once in a while I take a backup from my MAIN to my BACKUP computers. The problem is that in SQL Server 2014 Express, the service SQL Server Agent is not starting, in any way possible, I tried.

I want to be able to connect to my MAIN computer through network. All SQL Server 2014 data is connected well and working, plus all Services are running well, except for SQL Server Agent.

I see two solutions:

I downgrade my database from 2014 to 2012 and have SQL Server Agent Service Running, however I don't know how to do that.
I fix the SQL Server Agent service and have everything working well, I don't know how to do that either, I tried googling it, awkwardly I couldn't find an answer.

What does include SQL Server Agent? Can I have a download link? — Aboud, Sep 02 '15 at 17:37
Why do you need SQL Server Agent? I can't see anything in your question which requires SQL Server Agent... — larsts, Sep 02 '15 at 17:55
As of my recent tests, I got to discover that SQL Server Agent Services is responsible for receiving remote connections from another computer — Aboud, Sep 02 '15 at 17:57
Of course! check at this link https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx for futher info. — Renato Reyes, Sep 02 '15 at 18:00
I have SQL Server Browser running, I don't think this is the issue here — Aboud, Sep 02 '15 at 18:14
Fine. But SQL Server Agent is not responsible for handling remote connections. It is a service responsible for handling scheduled tasks. https://msdn.microsoft.com/en-us/library/ms189237.aspx — larsts, Sep 02 '15 at 18:22
Here is a good SO post about enabling remote connections, albeit for 2012 Express: http://stackoverflow.com/questions/11278114/enable-remote-connections-for-sql-server-express-2012 I think most of the information there is valid for 2014 as well. — larsts, Sep 02 '15 at 18:48

score 6 · Answer 1 · answered Sep 02 '15 at 20:25

SQL Agent is not available on SQL Server Express edition, only on Standard and Enterprise
If you had SQL Agent running in 2012, that implies you have either standard, bi or enterprise edition.
Downgrading a database to an older version is not an easy task. There is no direct way to do this. If you still have your 2012 backup and very little or nothing has changed since the upgrade, just restore the 2012 database and manually sync the databases (i.e. manually move data). If it's a non-trivial amount of changes, you'll need to do a database copy or export/import from 2014 to 2012.
If you want to setup a scheduled backup of the SQL Express database, you can create a task using Windows scheduler to execute the backup using sqlcmd. For example:

SqlCmd -E -S Server_Name –Q “BACKUP DATABASE your_prod_db TO DISK='D:\folder_on_local_drive\your_prod_db.bak'"
As hinted in the sample, you should backup to the local computer first then copy the backup file to your remote computer. This can be part of the same job (ideally) or a separate job (a bit of extra effort to get the timing right). This can significantly increase the success rate of getting a good backup plus it'll probably run a lot faster too.

SQL Server Agent not working in SQL Server 2014 Express

1 Answers1