SQL Backing Up Scheduled Jobs Manually Or By Script

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

Blocking / Locking or Unlocking pst files in Outlook

Blocking or removing

Set the value of each of the following registry keys to 0 (block) 1 (enable):

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\ArchiveDelete
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\ArchiveMount
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\ArchiveOld
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\DeleteExpired
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\DoAging
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\Preferences\PromptForAging

Now we're going to disable the use of .PST files on users' workstations. To do so, the administrative template for Microsoft Outlook must be installed.

Open the Group Policy Object Editor and navigate through the group policy tree to: User Configuration -> Administrative Templates -> Microsoft Office Outlook 2007 -> Miscellaneous -> PST Settings.

I recommend verifying that the group policy setting Prevent Users From Adding New Content to Existing .PST Files is still enabled. Next, enable the Prevent Users From Adding .PSTs to Outlook Profiles and/or Prevent Using Sharing-Exclusive PSTs.

When you enable this setting, you need to decide which setting you want to use. The default setting lets users continue to add .PST files. Therefore, simply enabling the policy setting doesn't help reach our goal.

The next option is to disallow the addition of .PST files. While this may seem like the best option, it does have some nasty side effects. If you block all .PST files, then some Microsoft Outlook features, such as SharePoint lists and Internet calendars, will cease to function.

The final option is to add only sharing-exclusive .PSTs. This is usually your best option because it prevents users from copying mail items to and from .PST files. It also won't prevent certain Outlook features from working.

I prefer to use the Group Policy Object Editor to lock down .PST files. However, some Exchange administrators prefer to use a registry setting that removes the Outlook Data File option from the menu when a user selects the New command from Outlook's File menu. If you want to try this approach, go to the following registry key and set its value to 5575:
HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12\Outlook\DisableCmdBarItemsList\TCID1

Create Unlock.reg file and run as follows (example Remove Block):

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Outlook\Preferences]
"DoAging"=dword:00000001