Scripting out several databases on a server

A while ago, I wrote an article Automated Script-generation with Powershell and SMO about using SMO to script out a SQL Server database. It has remained surprisingly but agreeably popular. SMO is still there, but now part of the sqlserver module that is included with SSMS and downloadable. Someone recently asked me whether it was possible to alter one of the scripts to allow several databases to be scripted out at one go. The answer of course, ‘Do bears deposit nitrogenous waste-products in the wood?’. I thought I ought to upgrade a script to current standards, and add support for those who have to use a SQL Server Login. I also initialize the scripting preferences in a different way that allows configuration to be read from disk or specified differently for each database. I haven’t implemented these configuration  options but merely made it possible by having the configuration data in a hash table, so it can be read in. 

Here is the code

$Filepath = 'PathToWhereToStoreThem' # local directory to save build-scripts to
$DataSource = 'MySQLServerInstance' # server name and instance
$Database = 'A*' # the database to copy from. wildcard comparison *, ? [a-d](range) and [and](set ofchars)
$SQLUserName = 'PhilFactor'#leave blank if Windows auth
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$popVerbosity = $VerbosePreference
$VerbosePreference = "Silentlycontinue"
# the import process is very noisy if you are in verbose mode
Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality
$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
  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") $DataSource }
else # if using sql server security we do it via a connection object
{
  $ServerConnection = new-object "$ms.Management.Common.ServerConnection" (
                                      $DataSource, $SQLUsername, $SqlCredentials.Password)
  $s = new-object ("$My.Server") $ServerConnection
}

if ($s.Version -eq $null) { Throw "Can't find the instance $Datasource" }
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
$MyPreferences = @{
  'ExtendedProperties' = $true; #we want extended properties scripted
    'DRIAll' = $true; #Yes, all the constraints
  '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 it means the script can be more versatile
}
$MyPreferences.GetEnumerator() |
Foreach{ $Name = $_.name; $CreationScriptOptions.$name = $_.Value }

$s.Databases | where name -like $Database |
foreach {
    write-verbose "now doing $($_.name)"
  $transfer = new-object ("$My.Transfer") $_
  $CreationScriptOptions.Filename = "$($FilePath)\$($_.Name)_Build.sql";
  $transfer.options = $CreationScriptOptions # tell the transfer object of our preferences
  $transfer.ScriptTransfer()
}
"All done, Master. "

SSMS uses the same library for scripting databases. By using code like this, you are just automating what would be a tedious and error-prone process. If you look at the original article, you’ll see that there are ways of doing an object-level script and so on, so you can match pretty-well everything you can do within SSMS.

The post Scripting out several databases on a server appeared first on Simple Talk.

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