Scripting out SQL Server Data as Insert statements via PowerShell

As a companion script with my recent article about  Scripting out several databases on a server, here is a similar  script that either scripts out the data for  the SQL Server tables you specify  as insert statements, or else as  native BCP files if they are too large for insert statements.

The purpose of scripting out the insert statements is that you can create a table that is designed for static data and fill it with the required data in the same script.   This can then be placed in source control and  the table will be filled with the data on build. Because this is only realistic with small tables, I’ve added the option to use native BCP. This is faster and takes between a quarter and a fifth of the disk space. 

I use wildcards to specify the databases because they are more intuitive for Ops people than RegExes, but obtusely, I use a Regex for the specification of the tables. This is because you can’t specify a list of alternatives in a Wildcard whereas it is easy with a RegEx.

<# script for writing out data from one or more databases either in insert statements
(useful for scripting small tables of static data) or in native BCP files.
You can use it to script the table followed by the data-insertion script if you set 
    'scriptSchema' = $true
Other types of BCP output are easy to do by altering the BCP parameters in the 
script slightly.
You can specify which tables you want to script out via a regex. You can use wildcards
to specify the databases
   #>

$Filepath = 'PathToWhereToStoreThem' # local directory to save build-scripts to
$DataSource = 'MySQLServerInstance' # server name and instance
$Databases = @('A*','MyDbase','Pubs') <# the databases to copy from. wildcard comparison *, ?
 [a-d](range) and [and](set of chars) #>
$SQLUserName = 'PhilFactor'#leave blank if Windows auth
$TablesRegex = '.*' # Regex match to specify tables
$UseBCP =$false;

# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
#load the sqlserver module
$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
# 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")
<# this is only needed if we are doing insert statements #>
$MyPreferences = @{
  'ScriptBatchTerminator' = $true; # this only goes to the file
  'ToFileOnly' = $true; #no need of string output as well
  'ScriptData' = $true;
  'scriptSchema' = $false;
  'Encoding' = [System.Text.Encoding]::UTF8;
}
$MyPreferences.GetEnumerator() |
  Foreach{ $Name = $_.name; $CreationScriptOptions.$name = $_.Value }

$possibilities = $s.Databases | select name
$DatabaseList = @()
$DatabaseList += $databases |
where { $_ -Notlike '*[*?]*' } |
where { $possibilities.Name -contains $_ }
$DatabaseList += $databases |
   where { $_ -like '*[*?]*' } |
     foreach{ $wildcard = $_; $possibilities.Name | where { $_ -like $wildcard } }
$DatabaseList | Sort-Object -Unique |
  foreach {
  write-verbose "now doing $($_)"
  $TheDatabase = $s.Databases[$_]
  
  $TheDatabase.Tables | where { $_.IsSystemObject -eq $false -and $_.name -match $tablesRegex } |
    foreach{
    <# calculate where it should be saved #>
    $directory = "$($FilePath)\$($s.Name)\$($TheDatabase.Name)\Data"
    <# check that the directory exists #>
    if (-not (Test-Path -PathType Container $directory))
    {
      <# we create the  directory if it doesn't already exist #>
      $null = New-Item -ItemType Directory -Force -Path $directory;
    }
    if ($UseBCP -eq $true) <# then we are doing a Native BCP#>
    {
      if ($SQLUserName -eq '')<# OK. Easy, a trusted connection #>
      {
        #native format -n, Trusted connection -T
        BCP "$($_.Schema).$($_.Name)"  out  "$($directory)\$($_.Schema)_$($_.Name).bcp"   `
          -n -T "-d$($TheDatabase.Name)"  "-S$($s.Name)"
      }
      else <# if not a trusted connection we need to provide a userid and password #>
      {
      $progress='';
        $Progress=BCP "$($_.Schema).$($_.Name)"  out  "$($directory)\$($_.Schema)_$($_.Name).bcp"  `
          -n "-d$($TheDatabase.Name)"  "-S$($s.Name)"  `
          "-U$($s.ConnectionContext.Login)" "-P$($s.ConnectionContext.Password)"
      }
      Write-verbose "Writing out to $($_.Schema).$($_.Name) $($directory)\$($_.Schema)_$($_.Name).bcp
      $progress"
      if (-not ($?)) # if there was an error
      {
        throw ("Error with data export of $($directory)\$($_.Schema)_$($_.Name).bcp ");
      }
      
    }
    else <# we are doing insert statements #>
    {
      $CreationScriptOptions.Filename =   `
        "$($FilePath)\$($s.Name)\$($TheDatabase.Name)\Data\$($_.Schema)_$($_.Name)_Data.sql";
      $scripter = new-object ("$My.Scripter") ($s)
      $scripter.Options = $CreationScriptOptions
      $scripter.EnumScript($_.Urn)
    }
  }
}
"I have done my best to obey, Master. "

I always think, each time I script a way of getting data from SQL Server, that it will be my last, but there always seems to be a new requirement that pops up. This time, it was for a script that would produce not only the table build statement but also the data insert statement. It is possible to do this in SSMS, though if you need to do more than a couple of tables it can get boring. It is possible to script any routine operation that you perform in SSMS because both PowerShell and SSMS use the SMO library to achieve their ends.  I personally would prefer to use a multi-statement VALUES derived table to insert data, because it is faster and more versatile. What? You want me to provide a routine that scripts out a table as a  multi-statement query? Maybe one day.

 

The post Scripting out SQL Server Data as Insert statements via PowerShell appeared first on Simple Talk.

from Simple Talk http://bit.ly/2YXg9JC
via