script needed urgent

Question

hi experts,

i have approx 200 jobs in production env.

can any one give me the script, which will script all sql jobs for me.. i wont need any manual process pls... any code..shell script which script all sql jobs so that i can have some sort of backup for them... msdb backup is not choice.

Answer 1

In SSMS, open "Object Explorer Details" by pressing F7.

Go to "SQL Agent" -> Jobs -> Select all jobs, Right click and "Script Job as" -> "Create to"

All, Jobs will be scripted at once.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page

Answer 2

In SSMS, open "Object Explorer Details" by pressing F7.

Go to "SQL Agent" -> Jobs -> Select all jobs, Right click and "Script Job as" -> "Create to"

All, Jobs will be scripted at once.

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page

i asked for automate process, its 200 jobs, cant spend time manually. i have written as well query which will give create script not the manual approach.

Answer 3

You may try the below link:

http://blogs.msdn.com/b/jenss/archive/2009/01/27/script-out-jobs-in-sql-server.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

You may try the below link:

http://blogs.msdn.com/b/jenss/archive/2009/01/27/script-out-jobs-in-sql-server.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

do you want me to run .exe file???

Answer 5

Check the below link, it will help you,

http://www.databasejournal.com/features/mssql/article.php/2205291/Generate-Scripts-for-SQL-Server-Objects.htm

---

Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

Answer 6

SQL Job is nothing but an object in SQL Server, below link will give you the code how to generate script of all the object, you just need to do following tweaking in the code as shown below  so that it will only give script of all the sql jobs

check this link http://www.databasejournal.com/features/mssql/article.php/2205291/Generate-Scripts-for-SQL-Server-Objects.htm, using the code posted on this link you can automate your process

exec proc_genscript 
	@ServerName = 'Server Name', 
	@DBName = 'Database Name', 
	@ObjectName = 'Object Name to generate script for', 
	@ObjectType = 'Job', --'Object Type', 
	@TableName = 'Parent table name for index and trigger',
	@ScriptFile = 'File name to save the script'

---

Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL

---

Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

Answer 7

it finally meet the requirement with a poweshell script only.