SSIS Package to Script All SQL Server Jobs to Individual Files
-
Why don't you post it? I am always interested in multiple ways of doing the same thing. This would only work for SQL Server 2008 and above because powershell is not available as a Job step type in SQL Server 2000 or 2005, right?
Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane -
Yes, please do post that powershell script.
This was a nice article - thanks for sharing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Yeh, was planning to post it, but was rushed with the earlier posting 😀
Just replace
"<< YOUR SERVER >>" with server name
"<< INSTANCE >>" with instance name, or DEFAULT if no instance.
"<< YOUR FOLDER >>" the folder you want the scripts saved in.
CD SQLSERVER:\SQL\<< YOUR SERVER >>\<< INSTANCE >>\JobServer\Jobs
$outDir = "<< YOUR FOLDER >>"
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("<< YOUR SERVER >>")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False
foreach ($Item in Get-ChildItem) {
$nm = $Item -replace ":", ""
$Scripter.Options.FileName=$outDir + "SQLAgentJob_" + $nm + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Options.ScriptDrops=$True;
$Scripter.Script($Item)
$Scripter.Options.AppendToFile=$True
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.ScriptDrops=$False;
$Scripter.Script($Item)
}
Couple of things I'll change when I get some spare time ( yeh right, who has spare time )
- Add server name/instance to file name
- Enhance the -replace part to do regex replace to remove invalid chars
- maybe see what static values can be replace with variables, or dynamic info
Enjoy
--------------------
Colt 45 - the original point and click interface
-
Thanks Philcart!
I really need to look at this powershell someday when I have some free time.
Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane -
Thanks Phil.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Some similar powershell scripts I cobbled together.
Script jobs and some other server objects I'm interested in:
Note that there should be as many "paths" as there are "output paths" if you add types to this.
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Paths = "SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Jobs","SQLSERVER:\SQL\localhost\DEFAULT\Logins","SQLSERVER:\SQL\localhost\DEFAULT\LinkedServers","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Accounts","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Profiles","SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Operators"
$OutputPaths = "Server\Jobs","Server\Logins","Server\LinkedServers","Server\Mail\Accounts","Server\Mail\Profiles","Server\Operators"
$i = 0
do
{
$path = $Paths[$i]
$Outputpath = "Source\SQL\"+$OutputPaths[$i]
new-item -Force -path D:\ -name $Outputpath -type directory
$dirname = "D:\" + $Outputpath
cd $path
foreach ($item in Get-Childitem)
{
if ($item.Name.Substring(0,2) -eq "##")
{
echo $item.Name
continue
}
$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$True
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.IncludeDatabaseRoleMemberships=$True
$Scripter.Options.IncludeDatabaseContext=$True
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True
$p = "D:\"+$Outputpath+"\"+$item.Name.Replace("\","_").Replace(" ","_")+".sql"
$Scripter.Options.FileName=$p
$Scripter.Script($Item)
}
$i++
}
while ($i -lt $Paths.Length)
Script database objects I'm interested in (including certain specific table bits like indexes and foreign keys)
Param
(
$Database
)
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$TableSubtypes = "Indexes","Triggers","ForeignKeys"
$ExcludedScriptTypes = "ApplicationRoles","AsymmetricKeys","Certificates","DatabaseAuditSpecifications","Defaults","ExtendedProperties","ExtendedStoredProcedures","FileGroups","LogFiles","SymmetricKeys","ServiceBroker"
$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database
cd $Path
foreach ($objectType in Get-ChildItem)
{
if ($ExcludedScriptTypes -contains $objectType)
{
continue
}
$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database+"\"+$objectType
cd $Path
$dirname = "Source\SQL\Databases\" + $Database + "\" + $objectType
new-item -Force -path D:\ -name $dirname -type directory
foreach ($Item in Get-ChildItem)
{
$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$True
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.IncludeDatabaseRoleMemberships=$True
$Scripter.Options.IncludeDatabaseContext=$False
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True
$modifier = ""
if ($Item.Schema -ne $null)
{
$modifier += $Item.Schema + "."
}
if ($Item.Name -ne $null)
{
$p = $Item.Name
$modifier += $Item.Name.Replace("\","_") + "."
}
$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$Database+"\"+$objectType+"\" + $modifier + "sql"
$p = "D:\Source\SQL\Databases\"+$database+"\"+$objectType+"\" + $modifier + "sql"
echo $p
$Scripter.Script($Item)
if ($objectType -eq "Tables")
{
foreach ($subtype in $TableSubtypes)
{
foreach ($subitem in $Item.$subtype)
{
$Scripter.Options.AnsiPadding=$False
$Scripter.Options.AnsiFile=$True
$Scripter.Options.AppendToFile=$False
$Scripter.Options.Bindings=$False
$Scripter.Options.DriAll=$False
$Scripter.Options.ExtendedProperties=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.IncludeIfNotExists=$False
$Scripter.Options.Permissions=$True
$Scripter.Options.SchemaQualify=$True
$Scripter.Options.ScriptData=$False
$Scripter.Options.ScriptDrops=$False
$Scripter.Options.Statistics=$False
$Scripter.Options.ToFileOnly=$True
$modifier = ""
if ($Item.Schema -ne $null)
{
$modifier += $Item.Schema + "."
}
if ($Item.Name -ne $null)
{
$modifier += $Item.Name.Replace("\","_") + "."
}
if ($subitem.Name -ne $null)
{
$modifier += $subitem.Name.Replace("\","_") + "."
}
if ($subtype -eq "Indexes")
{
if ($subitem.IsClustered)
{
$modifier += "Clustered."
}
if ($subitem.IndexKeyType -eq "DriPrimaryKey")
{
#Primary keys fall under indexes as well as primary key branches
$subtype = "PrimaryKeys"
} else
{
$subtype = "Indexes"
}
}
$dirname = "Source\SQL\Databases\" + $Database + "\" + $subtype
new-item -Force -path D:\ -name $dirname -type directory
$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"
$p = "D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"
echo $p
$Scripter.Script($subitem)
}
}
}
}
}
A sort of wrapper script to iterate through all the databases I want to script like this (e.g. nightly from a job), assuming of course you called the script above "ScriptDatabase.ps1" and stored in path shown :). This could be rolled into the above script, but wanted to retain ability to take a snapshot of a single database scripts.
$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases"
cd $Path
foreach ($item in Get-Childitem)
{
echo $item.Name
D:\Scripts\ScriptDatabase.ps1 $item.Name
}
All of these scripts write to specific folders on the D:\ of my servers but could be adapted.
Lookup $Scripter.Options on the net for parameters to pretty much mirror anything you can do via "Generate scripts" in SSMS
Obviously a disclaimer applies: This does not cover every aspect of every type of object in SQL Server and should not be relied on in lieu of a proper backup. Adjust this to ensure that permissions / extended properties / other object types that this script excludes are included appropriately.
Regards,
Mike
-
Thanks for this great article. Now i'm one step closer to source controlling everything in my SQL Production environment. Cheers
-
Its working fine ----- but if I have to change user_id and password what do I need to do.
-
Hi All,
Hopefully someone can help point me in the right direction. Let me just start by saying I am a complete newbie when it comes to scripting a task using SMO and VB
What I want to acheive is the following:
1. Read in SSIS variables:
- 'live' server
- 'mirror' server
- JobID
- Job name
2. connect to the 'Live server'
3. script a specific job using either the JobID or JobName.
4. Either assign this back out to an SSIS variable or transfer the job to the 'mirror server'.
I've now got the Script task to connect to the the required server. I belive I've also managed to find out how to filter for the job I want to script.
Where I am still confused and lost is being able to actually read the SQL Script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.common
Imports System.Collections.Specialized
Public Class ScriptMain
Public Sub Main()
'Get the Live/Mirror Server Address Details
Dim LiveServerName As String = Dts.Variables("LiveServerName").Value.ToString
Dim MirrorServerName As String = Dts.Variables("MirrorServerName").Value.ToString
'Get the JobID and Jobname that needs to be copied accross.
Dim Job_CopyJobToMirrorJobID As String = Dts.Variables("Job_CopyJobToMirrorJobID").Value.ToString
Dim Job_CopyJobToMirrorName As String = Dts.Variables("Job_CopyJobToMirrorName").Value.ToString
'Convert String to GUID - note: the string is already a correctly formated version of the job GUID
Dim GUID_CopyJobToMirrorJobID As New Guid(Job_CopyJobToMirrorJobID)
'Declare a ServerConnection object variable to specify SQL authentication, login and password.
Dim conn As New ServerConnection
conn.LoginSecure = False
'Connect to the local, default instance of SQL Server.
Dim LiveSvr As Server
LiveSvr = New Server(LiveServerName)
Dim MirrorSvr As Server
MirrorSvr = New Server(MirrorServerName)
'Script Out the Job
'Dim sc As stringCollection
Dim scrp As Scripter
scrp = New Scripter(LiveSvr)
scrp.Options.ScriptDrops = False
scrp.Options.ToString()
Dim sc As String
Dim JobScript As String
sc = LiveSvr.JobServer.Jobs.ItemById(GUID_CopyJobToMirrorJobID).Script().ToString()
MsgBox("SC: " & sc, MsgBoxStyle.OkOnly, "Script")
Dim st As String
For Each st In sc
JobScript = JobScript & st
Next
MsgBox("JobScript: " & JobScript)
'
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I'm struggling to get to grips with this at the moment, so any direction or help would be very appreciated.
Thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
-
any ideas anyone? .........
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
-
Great article. I'm actually working on something similar and I'm hoping you can help me out with one last small piece. I'm trying to pull the connection information from a connection manager. I can get the server name without a problem. Where I'm running into an issue is that I want to use SQL Auth and I can't get the username or password out of the connection manager. Currently I'm trying the following code:
Dim sourceConn As ServerConnection
sourceConn = DirectCast(Dts.Connections("SOURCE").AcquireConnection(Dts.Transaction), _
ServerConnection)
Dim src As New Server(sourceConn)
Unfortunately I get an error trying to convert my acquired connection into a ServerConnection object. I'm using an ADO.Net connection type.
Thanks for any help
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
I commented out a section in the code on how to use SQL Server Auth:
/* Setup connection, this is SQL Server authentication
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "YourUserName";
conn.Password = "YourPassword";
conn.ServerInstance = Dts.Variables["varServerName"].Value.ToString();
Server srv = new Server(conn);
*/
Just remove the trusted auth code and uncomment this section using the correct user name and password.
Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane -
Unfortunatly I'm having to use a sysadmin login and I don't want to hard code my password into the code. Is there a way to pull it from the connection manager? Or better yet have the connection manager make the connection and then use that connection?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
-
Sorry that is a classic problem with SQL Server logins. An option is to encrypt the password in a the config file and decrypt in the SSIS package using SQL Servers built in encryption/decryption or a home grown one using CLR sp or function. You can find info on both by doing a search here or on Google or Bing.
Thomas Lane, PhD
DBA
Edgenet / Big Hammer Data
SQL Lane -
Is there no way to generate the connection using the AcquireConnection method and pass it to the SMO server object? That way I don't have to mess with the password at all within my script.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply