SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Scripting & automating all SQL Server Jobs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

imransi17
Starting Member

12 Posts

Posted - 05/22/2013 :  12:25:14  Show Profile  Reply with Quote
Hello,

I am looking for an script to script out all the SQL Server agent jobs and automate it, for high availability purposes on StandBy Server.

Please help !

Thanks

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/22/2013 :  13:25:46  Show Profile  Reply with Quote
If you are replicating MSDB database, the agent jobs would get automatically ported. Assuming that is not the case, I don't know of a T-SQL way of generating all the scripts in one shot. Might be possible using Powershell/SMO. Googling for it, I see examples - here is one. I have not tried it. http://www.johnsansom.com/script-sql-server-agent-jobs-using-powershell/
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/22/2013 :  14:33:25  Show Profile  Reply with Quote
I would rather like to have further explanation of the term "a script to script out all the SQL Server agent jobs and automate it". SQL agent is used to Automate a task/job e.g. pick all the changes happened in tableA of ServerA and push them into tableA of ServerB. With that, what do you actually want to perform via a script which again automate agent jobs???

Cheers
MIK
Go to Top of Page

imransi17
Starting Member

12 Posts

Posted - 05/22/2013 :  14:37:27  Show Profile  Reply with Quote
here is the script that I found.... http://www.nilkanth.com/2004/07/18/sql-jobs-auto-backup/

but it does NOT return any value...hmmm?

keep in mind that I am using SQL 2008R2 Enterprise version.

Please help.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/23/2013 :  01:14:41  Show Profile  Reply with Quote
there's also transfer jobs task available in SSIS if you want to utilize. you can design a package to transfer the jobs and automate it in a separate job if you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000