0

We have SQL agent jobs which are AG aware: the 1st job step checks if the server is primary (if yes, then go to next step which is the actual job step), otherwise quit the job reporting success.

There is 1 database that isn't participating in the AG (the plan is to manually restore this db if there is a failover). There is a job on primary that invokes a SP from this db. I am asked to create this job on the secondary server (so that post failover we only have to restore the db without needing to re-create the job). Since the db doesn't exist on secondary, I'm not able to create a job for that db on the secondary. Even if I restore a copy of the db on secondary, create the job, and then delete the db, the database dropdown (within the job) becomes empty.

One solution is to manually create the job post failover after restoring this db. Is there any alternate solution which will allow me to create the agent job without the database existing?

1
  • 2
    Can you database-qualify the execution of the proc, i.e., having the job in the, say, master database? EXEC dbname.dbo.procname. Jul 3, 2023 at 11:23

1 Answer 1

3

The database dropdown in the Job Step window, is only to specify the execution context. It doesn't really matter what database you choose for the execution context if you fully qualify the object references of your code (as Tibor mentions), and as long as the executing user has access to that database.

A safe bet in your case would be to choose the master database and then either fully qualify the procedure name with the 3-part naming convention like so:

EXEC NonExistentDBName.SchemaName.ProcedureName;

Or just switch the executing database context as part of the code with a USE statement in your code like so:

USE NonExistentDBName;

EXEC SchemaName.ProcedureName;

I'd recommend the first option of fully qualifying the object reference.

2
  • Any reason for recommending first option?
    – variable
    Jul 3, 2023 at 14:12
  • @variable Nothing materially other than it's a single line of code, and less awkward than changing the database context within the Job Step textbox. I'm sure someone smarter than me may have better reasons.
    – J.D.
    Jul 3, 2023 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.