Here are two different ways to backup all scheduled jobs on an instance. The first is done manually the second is scripted into a vbscript and can be scheduled.
[MANUAL]
Pull up the instance >> SQL Agent >> Highlight Jobs
hit F7
Highlight all jobs
Create Scripts
[SCRIPTED]
This sample will create the .sql scripts and place them in an off server depot for restoring if needed. The scripts are also added to a database for restoring.
ON ERROR RESUME NEXTDIM DestinationFolderCONST ROOTFOLDER = "\\DestinationServer\Share$\SQL_JOBS_BACKUP\"CONST
NumDaysCleanup = 10''===// Retrieve Data from Database //=====Const DBSERVER = "mySQLinstance.myCompany.net"Const DATABASE = "myDatabase"Const DBUSERID = "MyID_ReadOnly"Const DBPASSWD = "MyID_Pa$$word"SQLQuery = "SELECT [SQL Server Name], [ApplicationType] FROM [CTS_DIO_SQL].[dbo].[V_SupportedInstances]" ''' Construct ConnectionStringszADOConn =
DBConn.open szADOConn
szSqlQuery = SQLQuery
cmd.CommandType = &H0001 "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_"Data Source=" & DBSERVER & ";" &_"DATABASE=" & DATABASE & ";" &_"User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" Set DBConn = CreateObject("ADODB.Connection")Set cmd = CreateObject("ADOdb.Command")''adCmdTextcmd.ActiveConnection = DBConn
cmd.CommandText = szSqlQuery
SQLServerName = Recordset(
DestinationFolder = ROOTFOLDER & Recordset(Set RecordSet = CreateObject("ADODB.Recordset")Set RecordSet = cmd.Execute(szSQLlc)Dim oSQLDMO, oStream, idStep, ScriptJob, CountJobs, JobName, ScriptAllJobs Set oStream = CreateObject("ADODB.Stream") Set objfso = CreateObject("Scripting.FileSystemObject")Set oSQLDMO = CreateObject("SQLDMO.SQLServer") Do While Not RecordSet.EOF"SQL Server Name")"ApplicationType") & "\" & Replace(SQLServerName, "\", "$") & "\"WSCRIPT.ECHO ""WSCRIPT.ECHO SQLServerName
' Connect via DMO to the SQL ServeroSQLDMO.LoginSecure = trueoSQLDMO.LoginTimeout = 60
oSQLDMO.Connect SQLServerName
WScript.Echo err.number' Create the folder if it does not exist
CALL CreateFolderStructure(DestinationFolder)' Delete Aged Files
Wscript.Echo
strYear = Right(DatePart(
strMonth = Right(100+DatePart(
strDay = Right(100+DatePart(
strFilename = CALL Cleanup_history(SQLServerName, NumDaysCleanup)"---------> WRITING: scripting out backup scripts...." Dim strYear, strMonth, strDay"yyyy",Date), 4)"m",Date),2)"d",Date),2)"[" & strYear & "-" & strMonth & "-" & strDay & "]"CountJobs = 0
CountJobs = oSQLDMO.JobServer.Jobs.Count For Each oJob In oSQLDMO.JobServer.Jobs Next
JobName = oSQLDMO.JobServer.Jobs.Item(idStep).Name
ScriptJob = oSQLDMO.JobServer.Jobs.Item (idStep).Script(4, DestinationFolder & strFilename &
ScriptAllJobs = ScriptAllJobs & ScriptJob
ScriptJob = LEFT(ScriptJob, instrRev(ScriptJob,
ScriptJob = REPLACE(ScriptJob,
SQL_Insert =
For idStep = 1 To CountJobs " " & JobName & ".sql") "GO") -1)"'", "''")"INSERT INTO [CTS_DIO_SQL].[dbo].[tblSQL_Inventory_DYNAMIC_ScheduledJobs_Scripts] " &_"([SQL Server Name], Job_Name, CombinedScript, Script, Backup_Date) " &_"SELECT '" & SQLServerName & "', '" & JobName & "', 0, '" & ScriptJob & "', GetUTCDate() "
FullPath = DestinationFolder & strFilename & CALL Write_To_Database(SQL_Insert) Next " 0_AllJobs_Backup.sql"CALL Write_To_File(FullPath, ScriptAllJobs) ' Write the script to a fileScriptAllJobs = REPLACE(ScriptAllJobs,
SQL_Insert =
"'", "''")"INSERT INTO [CTS_DIO_SQL].[dbo].[tblSQL_Inventory_DYNAMIC_ScheduledJobs_Scripts] " &_"([SQL Server Name], Job_Name, CombinedScript, Script, Backup_Date) " &_"SELECT '" & SQLServerName & "', 'Combined Job Backup Script', 1, '" & ScriptAllJobs & "', GetUTCDate() "CALL Write_To_Database(SQL_Insert) ' Write the script to database.
SQLServerName = ""ScriptAllJobs = ""oSQLDMO.DisConnect
RecordSet.movenextLoop
Set oStream = Nothing Set oSQLDMO = NothingSet RecordSet = NothingSet cmd = NothingDBConn.closeSet DBConn = Nothing
Sub Cleanup_History(SQLServerName, NumDaysCleanup)' Cleanup FolderIf objfso.FolderExists(DestinationFolder) Then
WSCRIPT.ECHO
Set objFolder = objfso.GetFolder(DestinationFolder)"---------> CLEANUP: Deleting files older than " & NumDaysCleanupCall CheckFolder(objFolder, NumDaysCleanup, True)ElseWScript.Echo "The specified folder", DestinationFolder, "does not exist"
SQL_Delete =
End If "DELETE FROM [CTS_DIO_SQL].[dbo].[tblSQL_Inventory_DYNAMIC_ScheduledJobs_Scripts] " &_"WHERE [SQL Server Name] = '" & SQLServerName & "' " & _" AND ([Backup_Date] < GetUTCDate() - " & NumDaysCleanup & " " &_" OR DateDiff(d, [Backup_Date], GetUTCDate()) = 0 )"Write_To_Database(SQL_Delete)
End Sub Sub CheckFolder(objFolder, NumDaysCleanup, bRecurse)Set colFiles = objFolder.FilesIf colFiles.Count > 0 Then
For Each objFile In colFilesCall DeleteOldFile(objFile, NumDaysCleanup)NextElseWScript.Echo "No files in folder", objFolder.PathEnd IfIf bRecurse Then
Set colSubfolders = objFolder.SubFoldersIf colSubfolders.Count > 0 Then
CheckFolder SubFolder, NumDaysCleanup, For Each SubFolder In colSubfoldersTrueNextEnd IfEnd IfEnd Sub
Sub DeleteOldFile(objFile, NumDaysCleanup)On Error Resume NextErr.Clear
dateFile = objFile.DateLastModified
dateToday = Now()If dateFile <= dateToday ThendaysOld = dateToday - dateFileIf daysOld > NumDaysCleanup ThenstrFile = objFile.Path
objFile.Attributes = 0
objFile.DeleteIf Err.number <> 0 ThenWScript.Echo Err.number, Err.Description, Err.Source, strFile
Call RemoveOnReboot(strFile)End IfEnd IfElseWScript.Echo "Incorrect date stamp in", strFileEnd IfEnd Sub
strComputer = Sub RemoveOnReboot(strFile)Const HKLM = &H80000002"."
strKey = Set objReg = GetObject("winmgmts:" & strComputer & "\root\default:StdRegProv")"SYSTEM\CurrentControlSet\Control\Session Manager"strName = "PendingFileRenameOperations"objReg.GetMultiStringValue HKLM, strKey, strName, arrValuesIf IsNull(arrValues) ThenarrValues = Array("\??\" & strFile, "")Else
arrValues(
arrValues(ReDim Preserve arrValues(UBound(arrValues) + 2)UBound(arrValues) - 1) = "\??\" & strFileUBound(arrValues)) = ""objReg.DeleteValue HKLM, strKey, strNameEnd IfobjReg.SetMultiStringValue HKLM, strKey, strName, arrValuesEnd Sub
oStream.Open
oStream.WriteText (TextContents)
oStream.SaveToFile (DestinationFile), 2
oStream.Close
oSQLDMO.DisConnect
Function Write_To_File(DestinationFile, TextContents)Dim oStream Set oSQLDMO = CreateObject("SQLDMO.SQLServer") Set oStream = CreateObject("ADODB.Stream")Set oStream = Nothing End Function
CreateFolderStructure = Function CreateFolderStructure(pFolderPath)Dim objFSOSet objFSO = CreateObject("Scripting.FileSystemObject") FalseIf Not objFSO.FolderExists(pFolderPath) ThenIf CreateFolderStructure(objFSO.GetParentFolderName(pFolderPath)) ThenCreateFolderStructure = True
Call objFSO.CreateFolder(pFolderPath)End IfElseCreateFolderStructure = TrueEnd If
End Function Function Write_To_Database(SQL_Select)'WScript.Echo SQL_Select
objConnection.Open _
Const adOpenStatic = 3Const adLockOptimistic = 3Set objConnection = CreateObject("ADODB.Connection")Set objRecordSet = CreateObject("ADODB.Recordset")"Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" & _"Data Source=applicationsql102.jpmchase.net;" & _"User ID=GDCIOWebsite_Writer;Password=Write$;"objRecordSet.Open SQL_Select, objConnection, adOpenStatic, adLockOptimistic
