|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:18 AM
Points: 110,
Visits: 162
|
|
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
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Friday, October 19, 2012 3:09 PM
Points: 24,
Visits: 197
|
|
| Thanks for this great article. Now i'm one step closer to source controlling everything in my SQL Production environment. Cheers
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 1:21 PM
Points: 11,
Visits: 270
|
|
| Its working fine ----- but if I have to change user_id and password what do I need to do.
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:47 AM
Points: 380,
Visits: 1,014
|
|
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)
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:47 AM
Points: 380,
Visits: 1,014
|
|
any ideas anyone? .........
_____________________________________________________________________________ MCITP: Business Intelligence Developer (2005)
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 3,367,
Visits: 1,567
|
|
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 Fisher I strive to live in a world where a chicken can cross the road without being questioned about its motives. -------------------------------------------------------------------------------- For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/ For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Link to my Blog Post --> www.SQLStudies.com
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:26 AM
Points: 239,
Visits: 317
|
|
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
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 3,367,
Visits: 1,567
|
|
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 Fisher I strive to live in a world where a chicken can cross the road without being questioned about its motives. -------------------------------------------------------------------------------- For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/ For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Link to my Blog Post --> www.SQLStudies.com
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:26 AM
Points: 239,
Visits: 317
|
|
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
|
|
|
|
|
Hall of Fame
       
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 3,367,
Visits: 1,567
|
|
|
|
|