In order for a database system to work, you often need to provide programmable server objects. I’ve written very few databases that didn’t include agent jobs and triggers, or that didn’t require XEvents for diagnostics. These need to be scripted out in just the same way as database objects. . It can be done via SSMS, of course, but a script provides a safety-net even then. For a DBA, it is useful to script server objects.
You may be wanting to save just the server objects that are associated with one or more databases. It is always a problem with server scripts that it isn’t always easy to associate agent job steps with a particular database. I don’t have an answer for this. I think the best approach is to filter jobs by name if you have a job that has a one-to-one relationship with a database.
Here is an example of how to script out the main server programmable objects with the sqlserver module. I’ve included the empty databases in this script, since these are server objects, but you can experiment with the list according to your specific requirements. I’ve chosen to script out the server objects that aren’t system objects. You will get more informationabout settings and configurations if you remove that filter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
$Filepath = 'MyFilePath' # local directory to save build-scripts to $ServerInstance = 'MyServer' # server name and instance $SQLUserName = 'MyLogin' #leave blank if Windows auth $BadChars = '[\\\/\:\.]' #characters that we don't want in filenames set-psdebug -strict # to catch subtle errors $ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs # Load sqlserver module $popVerbosity = $VerbosePreference #remember current verbosity setting $VerbosePreference = "Silentlycontinue" # the import process is very noisy if you are in verbose mode Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.XEvent") -eq $null) { throw "Could not load library for Extended Events." } $VerbosePreference = $popVerbosity #Does the directory specified actually exist? If not create it. if (-not (Test-Path -PathType Container $Filepath)) { # we create the directory if it doesn't already exist $null = New-Item -ItemType Directory -Force -Path $Filepath; } # get credentials if necessary if ($SQLUserName -ne '') #then it is using SQL Server Credentials { $SqlEncryptedPasswordFile = ` "$env:USERPROFILE\$($SqlUserName)-$($SQLInstance).txt" # test to see if we know about the password in a secure string stored in the user area if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf) { #has already got this set for this login so fetch it $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString $SqlCredentials = ` New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted) } else #then we have to ask the user for it { #hasn't got this set for this login $SqlCredentials = get-credential -Credential $SqlUserName $SqlCredentials.Password | ConvertFrom-SecureString | Set-Content $SqlEncryptedPasswordFile } } $ms = 'Microsoft.SqlServer' $My = "$ms.Management.Smo" # if ($SQLUserName -eq '') #dead simple if using windows security { $s = new-object ("$My.Server") $ServerInstance } else # if using sql server security we do it via a connection object { $ServerConnection = new-object "$ms.Management.Common.ServerConnection" ( $ServerInstance , $SQLUsername, $SqlCredentials.Password) $s = new-object ("$My.Server") $ServerConnection } $ScriptOptions = new-object ("$My.ScriptingOptions") $MyPreferences = @{ #create the scripting options just the once 'ExtendedProperties' = $true; #we want extended properties scripted 'DRIAll' = $true; #Yes, all the constraints 'ScriptDrops' = $false; 'Indexes' = $true; # Yup, these would be nice 'Triggers' = $true; # This should be included when scripting a database 'ScriptBatchTerminator' = $true; # this only goes to the file 'IncludeHeaders' = $true; # of course 'ToFileOnly' = $true; #no need of string output as well 'IncludeIfNotExists' = $true; # not necessary but the script can be more versatile 'IncludeDatabaseContext' = $true; 'Encoding' = [System.Text.Encoding]::UTF8; } $MyPreferences.GetEnumerator() | Foreach{ $Name = $_.name; $ScriptOptions.$name = $_.Value } # if ($s.Version -eq $null) { Throw "Can't find the instance $ServerInstance " } $Scriptdirectory = "$FilePath\$($s.DomainInstanceName -replace $BadChars, '-')" <# and we can now establish the filename based on the server and maybe create the directories #> if (-not (Test-Path -PathType Container $Scriptdirectory)) { # we create the directory if it doesn't already exist $null = New-Item -ItemType Directory -Force -Path $Scriptdirectory; } $ScriptOptions.Filename = "$Scriptdirectory\Agent.sql"; $s.jobserver.script($ScriptOptions) #script out the agent creation (its properties, in fact!) #properties, roles, @('Databases', 'audits', 'AvailabilityGroups', 'CryptographicProviders', 'Endpoints', 'Settings', 'Triggers', 'BackupDevices', 'LinkedServers', 'Logins', 'UserDefinedMessages') | foreach { $s."$_" } | # we can get a description of each type from the URN and write each object out foreach { $currentCollection = $_.urn.Type; if ($_.IsSystemObject -eq 0) { $ScriptOptions.Filename = "$Scriptdirectory`\$currentCollection-$($_.Name -replace $BadChars, '-').sql"; $ScriptOptions.ScriptDrops = $true; $ScriptOptions.AppendToFile = $false; try { $_.Script($ScriptOptions) } catch { write-warning "$currentCollection : $($_)" } #delete server object if it is there $ScriptOptions.ScriptDrops = $false; $ScriptOptions.AppendToFile = $true; try { $_.Script($ScriptOptions) } catch { write-warning "$currentCollection : $($_)" } #create server object if not there } } #iterate over the collections we want to script out... @('Alerts', 'Jobs', 'Operators', 'SharedSchedules', 'ProxyAccounts', 'TargetServers') | foreach { $s.JobServer."$_" } | # all the Agent objects we want to script out foreach { $currentJobserverCollection = $_.urn.Type; $ScriptOptions.Filename = "$Scriptdirectory`\$currentJobserverCollection-$($_.Name -replace $BadChars, '-').sql"; $ScriptOptions.ScriptDrops = $true; $ScriptOptions.AppendToFile = $false; try { $_.Script($ScriptOptions) } catch { write-warning "$currentJobServerCollection : $($_)" } #script delete agent object if it is there $ScriptOptions.ScriptDrops = $false; $ScriptOptions.AppendToFile = $true; try { $_.Script($ScriptOptions) } catch { write-warning "$currentJobserverCollection : $($_)" } #create agent object } #Now we script out the Extended events (xEvents) #first we get our connection $SqlConn = $s.ConnectionContext.SqlConnectionObject #we use this to instantiate our Xtended Events store $store = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $SqlConn #we script out all the sessions. $store.Sessions | foreach{ $_.ScriptCreate().ToString() > "$Scriptdirectory`\Events-$($_.Name -replace $BadChars, '-').sql"; } |
This should end up providing you with a directory with all your programmable server objects scripted out, each in its own file.
Load comments