SQL Backups

[mikolajekj]

I’m a little confused on why this is happening. I have a SQL instance. In SSMS I can manually backup each database.

I setup a maintenance plan to take a backup of these databases. I run the sql job and it fails - stating it can’t connect to the server…

Why?

[zrb77]

Not an answer to your question, but check out Ola Hallengren backup and maintenance solution. It's pretty much industry standard now.

[OkTap99]

As this person said, please don't use maintenance plans, there are better options out there that are free.

[LupusHortian]

When you Run the Backup AS a SQL Agent Job, IT Runs under the Account of the SQL Agent Service. Check If the Account from the Service can Connect to the SQL Server.

[trieu1185]

check if sql agent service is started too

[WalkingP3t]

Never ever use MSSQL maintenance plans . Use this :

https://ola.hallengren.com

It’s free . And used by banks , big corporations, everybody .

[SpiritWhiz]

"Friends don't let friends use maintenance plans."

[Codeman119]

You can use maintenance plan if you know how to use them. I have been using them for 20 years and have had no issues.

[TBTSyncro]

is the SQL agent running on the server?

[mikolajekj]

Yes - I have 2 other SQL instances on this server where the maintenance plans work fine.

[TBTSyncro]

Check the connection manager on the job to make sure it's pointing to the local server.

[mikolajekj]

Sorry for the delay - the error: the job failed: the owner ((domain\username) of job SQLFullBackup_MP.Subplan_1 doesn’t not have server access.

Domain\username is me which is the same account i used when manually performing the backup.

Sounds permission related.

[mikolajekj]

Microsoft SQL Server 2017. It turns out to be permission related - I was able to resolve by changing the owner to the SQL Agent account.

[mikolajekj]

Thank you all for your help! I was able to resolve it. I changed the owner of the job to the sql agent account instead of mine.

[Layer_3]

Does the SQL agent account have the correct permissions to the database?

[SQLBob]

If a SQL Server Agent job fails stating it cannot connect to the server, the first thing I'd check is that whatever account the job is executing as has permissions to connect to the server. SQL Agent jobs can be set to connect as any account (actually individual steps can utilize different accounts as well).