Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Pulling off agent Jobs to File Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 4:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:45 AM
Points: 15, Visits: 140
Hello I just started a new position and I have been asked to go through all our SQL Servers and pull a copy of all the SQL Server Agent Jobs off to a file. I started this by just using SSMS and "Right Clicking" each Job then selecting Script Job as > Create To > File.
I was wondering is anyone had a script or that can automatically do this for all jobs on the Server? We have over 100 Servers in my new environment and some have as many as 50 jobs on them.

Any help with this would be greatly Appreciated.

Thanks
Post #1516970
Posted Sunday, November 24, 2013 10:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
1 level of improvement is to display the job list in the "Object Explorer" pane, highlight all the jobs at once, then "Create to File" and put them all in 1 file for that server.

I do that once in a while when I want to search all the jobs for something specific.



Post #1517102
Posted Sunday, November 24, 2013 6:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
jameslauf (11/22/2013)
I have been asked to go through all our SQL Servers and pull a copy of all the SQL Server Agent Jobs off to a file.


Unless they told you "Why", that's a fairly ridiculous request. What do they indend to achieve from such a thing? There may be much better methods than the simple listing of SQL Server Agent jobs but they must first define what they seek.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1517145
Posted Monday, November 25, 2013 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:45 AM
Points: 15, Visits: 140
We are getting ready to Sunset several Servers here and we are under several regulations so I believe it is for documentation purposes.

I am new enough to this company and my DBA experience until now has been on small envinronments like 2 SQL servers and max 5 Databases.

Post #1517433
Posted Monday, November 25, 2013 12:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:45 AM
Points: 15, Visits: 140
I was able to Highlight all and copy to single file. Thanks

I am still hoping to find a way to automate this more. All help is appreciated.
Post #1517436
Posted Tuesday, November 26, 2013 4:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:28 AM
Points: 102, Visits: 1,050
Here is the script that i found ..

http://www.nilkanth.com/2004/07/18/sql-jobs-auto-backup/

Then put this script in a job and schedule it to receive the output .


Regards,
Kumar
Post #1517600
Posted Tuesday, November 26, 2013 6:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
I have a couple of powershell scripts which I can use to do this. I use them when I take over a role if there is little source control and just as a precaution. They are useful for migrations too.

I forget where I got this one so I can't give credit to the initial author. I added a few modifications to it so...

# Date:     23/02/12 
# Description: PS script to generate all SQL Server Agent jobs on the given instance.
# The script accepts an input file of server names.
# Version: 1.0
#
# Example Execution: .\Create_SQLAentJobSripts.ps1 .\ServerNameList.txt

#param([String]$ServerListPath)

#Load the input file into an Object array
$ServerNameList = "";
#get-content -path $ServerListPath

$login = Read-Host 'Enter your SQL login'
$password = Read-Host 'Enter your password' -AsSecureString;

$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password);
$PlainPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR);

#Load the SQL Server SMO Assemly

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

#For each server in the array do the following..
foreach($ServerName in $ServerNameList)
{
Try
{
#$objSQLConnection.ConnectionString = "Server=$ServerName;Integrated Security=SSPI;"
$objSQLConnection.ConnectionString = "Server=$ServerName;User Id=$login;Password=$PlainPassword"
Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
$objSQLConnection.Open() | Out-Null
Write-Host "Success."
$objSQLConnection.Close()
}
Catch
{
Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
$errText = $Error[0].ToString()
if ($errText.Contains("network-related"))
{Write-Host "Connection Error. Check server name, port, firewall."}

Write-Host $errText
continue
}

#IF the output folder does not exist then create it
$OutputFolder = "C:\Scripts\Jobs\$ServerName"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

#Create a new SMO instance for this $ServerName
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $objSQLConnection

#Script out each SQL Server Agent Job for the server
$srv.JobServer.Jobs | foreach {$job = $_.name -replace "\*", ""; $_.Script() | out-file "$OutputFolder\$job.sql"}
}


I'll need to try and find the other one. Its much easier to follow and uses SQLPSX.
Post #1517652
Posted Tuesday, November 26, 2013 9:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:45 AM
Points: 15, Visits: 140
Thank You very much. Both of these should do the job. I will post once I have tried them.

Post #1517730
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse