February 27, 2009 at 3:36 pm
I have 2 servers and they are mirrored. My concern is the SQL agent job are not excepted from mirroring.
Is there a way SQL agent jobs can be mirrored too?
Thanks!
March 2, 2009 at 10:35 am
Database mirroring only mirrors user databases. The only way I can think of to copy jobs is to build and schedule a script that scripts them and runs the script on the standby server.
I've found that jobs don't change that often so it's may be more efficient to just store the job scripts in some kind of version control store like Visual SourceSafe and update them when you make a change to a job.
Greg
March 2, 2009 at 11:58 am
Thank you!
can you give me an idea or T-sql code on how to convert import sql server agent job. thanks!
March 2, 2009 at 2:30 pm
Sorry, I don't have a T-SQL script for this because I've never needed to do it on an on going basis. I've only scripted jobs to as a one-time thing when migrating to a new server so I've just scripted them in Enterprise Manager (SQL 2000) or Management Studio (SQL 2005).
Searching the SSC script section might turn up a script for you.
Greg
November 28, 2011 at 6:43 am
http://support.microsoft.com/kb/314546
Step 4: How to move jobs, alerts, and operators
Step 4 is optional. You can generate scripts for all jobs, alerts and operators on the source server, and then run the script on the destination server.
•To move jobs, alerts and operators, follow these steps:
1.Open the SQL Server Enterprise Manager, and then expand the Management folder.
2.Expand SQL Server Agent, and then either right-click Alerts, Jobs, or Operators.
3.Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, or Alerts, or Operators.
Jamie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply