Get DefaultFile and DefaultLog for a SMO server, in PowerShell

2015/12/09

When working with SMO server object in PowerShell, some properties are not well populated, depending on the version, install location, or … who knows what other reasons.
The way to overpass that is to use some related properties, but be aware that they might not always meet your needs.

# Load assemblies
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoExtended”) | Out-Null
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SmoEnum”) | Out-Null

# Create sql server object
$server = New-Object (“Microsoft.SqlServer.Management.Smo.Server”) “your server”

function Get-SqlServerDefaultFilePath($server) {
  # Determines the DefaultFile value for a SMO server,
  # given the fact that sometimes the value is not populated
  $defaultPath = ”
  if ($server) {
    $defaultPath = $server.DefaultFile
    if ($defaultPath.Length -eq 0) {
      $defaultPath = $server.Settings.DefaultFile
    }
    if ($defaultPath.Length -eq 0) {
      $defaultPath = $server.MasterDBPath
    }
    if ($defaultPath.Length -eq 0) {
      $defaultPath = $server.Information.MasterDBPath
    }
    if ($defaultPath.Length -eq 0) {
      $defaultPath = $server.InstallDataDirectory
    }
  }
  return $defaultPath
}

function Get-SqlServerDefaultLogPath($server) {
  # Determines the DefaultLog value for a SMO server,
  # given the fact that sometimes the value is not populated
  $defaultLog = ”
  if ($server) {
    $defaultLog = $server.DefaultLog
    if ($defaultLog.Length -eq 0) {
      $defaultLog = $server.Settings.DefaultLog
    }
    if ($defaultLog.Length -eq 0) {
      $defaultLog = $server.MasterDBLogPath
    }
    if ($defaultLog.Length -eq 0) {
      $defaultLog = $server.Information.MasterDBLogPath
    }
    if ($defaultLog.Length -eq 0) {
      $defaultLog = $server.InstallDataDirectory
    }
  }
  return $defaultLog
}

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.

Advertisements

Check if SQL Server supports CompressionOption in PowerShell

2015/11/11

You have a PowerShell script that does a database backup using SMO API.
You want to have a compressed backup, in order to save space.
You use

$smoBackup = New-Object (“Microsoft.SqlServer.Management.Smo.Backup”)
$smoBackup.Action = “Database”
$smoBackup.BackupSetDescription = “Full Backup of ” + $dbName
$smoBackup.BackupSetName = $dbName + ” Backup”
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = “Disk”
$smoBackup.Devices.AddDevice($targetPath, “File”)
$smoBackup.CompressionOption = 1

Cool, right ?
Wrong !

You might get an error like
BACKUP DATABASE WITH COMPRESSION is not supported on Express Edition (64-bit).
BACKUP DATABASE is terminating abnormally.

This is because several SQL Server versions / editions do not support the CompressionOption, among which SQL Server Express is one of them.
In order to check if the SQL Server supports the backup compression option, use the following script:

$compressOpt = -1 # Means that CompressionOption is not present
try {
  # For the line below, you could also use -DisableNameChecking,
  # but in my case, inside Chef using PowerShell script, was crashing
  Import-Module ‘sqlps’ -EA 0 -WA 0 > $null
  # Check to see if we have indeed backup CompressionOption
  $compressOpt = (Invoke-Sqlcmd -Query “SELECT ISNULL((SELECT value FROM sys.configurations WHERE name = ‘backup compression default’),-1) As CompressionOption;” -ServerInstance $serverName).CompressionOption
}
catch {
}

if ($compressOpt -gt -1) { # Means that we can use backup comppresion
  $smoBackup.CompressionOption = “1”
}

Note 1: Credits go to StackOverflow, IT blogs and alike, sites that helped me with the code when searching for solutions.
Note 2: Be aware that when copy-pasting, the quotes and possibly other characters get messed up by WordPress, you’ll have to replace them.


%d bloggers like this: