CODEWITHSUNDEEP
sql-serverserveragent
Aboud
Aboud

Reputation: 104

SQL Server Agent not working in SQL Server 2014 Express

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:

  1. I downgrade my database from 2014 to 2012 and have SQL Server Agent Service Running, however I don't know how to do that.

  2. 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.

Upvotes: 2

Views: 20535

Answers (1)

SQLmojoe
SQLmojoe

Reputation: 2053

  1. SQL Agent is not available on SQL Server Express edition, only on Standard and Enterprise
  2. If you had SQL Agent running in 2012, that implies you have either standard, bi or enterprise edition.
  3. 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.

  4. 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'"

  5. 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.

Upvotes: 6

Related Questions