Scripting Out a SQL Server instance (Agent jobs, XEvents, Triggers and the like)

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.

$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.

 

 

The post Scripting Out a SQL Server instance (Agent jobs, XEvents, Triggers and the like) appeared first on Simple Talk.

from Simple Talk https://ift.tt/2Fy7k0O
via