backupsql serversql-server-2008-r2sql-server-agent
Goal:
Reuse the SQL server agent jobs list.
Problem:
The list contains a lot of jobs and I need to reuse the jobs list in a another time without recreating it.
Based on the example of the picture, can I get the sourcecode of every job and enable reusing it when I establish a new database from scratch?
I would prefer not to recreate the job list from scratch.
Information:
*The list contains a lot of jobs.
Is the error you see on that last line always preceded by the same set of jobs, or does the error happen in different places?
I've thought about this for the last half hour and everything I think of I can refute. Do you have any known networking issues on this server? I've seen something kind of like this with VMWare's networking stack being flaky and dropping connections. But then that doesn't make sense because the SQL box is showing an error.
Even though I ask about networking, I can come up with refutations to that, too. This is a tough one.
If the jobs listed above are the culprit, then I would need to see what they are doing be sure what was going on.
What you can do is to have below script stored on your server or make it as a stored procedure :
use below with sqlcmd
IF EXISTS ( SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
END
ELSE
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'databaseMail test profile', --<-- Change HERE !!
@recipients = 'databaseteam@somecompany.com', --<-- Change HERE !!
@body = 'Please check the status of SQL Agent. It is not running !',
@query = 'SELECT @@SERVERNAME AS [InstanceName], 0 AS [SQLServerAgentRunning]',
@subject = 'SQL Agent is not running',
@attach_query_result_as_file = 0 ; -- set it to 1 to receive as txt file attachment
END
What you can do to alleviate it :
Best Answer
I've built a command-line utility that creates a .sql script file containing T-SQL that will recreate all SQL Server Agent Jobs for the specified SQL Server instance.
The utility takes several command-line parameters, as below, which necessitates running it from the command prompt,
cmd.exe.Be aware that this tool comes with no warranties, and is use at-your-own-risk. I have used it on my own SQL Servers, and it works for me.
The utility uses the Microsoft.SqlServer.Management.Smo.Agent.Job.Script method to output T-SQL, so the output should be precisely what is needed to recreate jobs.
You can download the binary and prerequisites from http://blog.mvct.com/technology/tool-for-scripting-sql-server-agent-jobs/
There are also many commercially available products that provide various features to move or copy jobs from server to server, such as the Idera SQL Admin Toolset. I neither work for, nor am I associated with Idera in any way.