Reputation: 1
I'm writing some Powershell scripts to manage a SQL Server. When I create my SMO object, I do a test to see if I can list my databases. If so, then I assume I'm connected and call a function (via an included file) that has an Invoke-Sqlcmd
that causes my database to disconnect or something.
However, if I run the invoke command directly in the script, it works fine.
Looking at the provided code, my output is as follows:
I'm connected $SqlServer is not contactable
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$ErrorActionPreference = "Stop"
Import-Module 'sqlps' -DisableNameChecking #load all of SMO
$ScriptDirectory = Split-Path -Path $MyInvocation.MyCommand.Definition -
Parent
try {
("$ScriptDirectory\_HRbackup.ps1")
("$ScriptDirectory\_HRprinter.ps1")
("$ScriptDirectory\_HRbody.ps1")
} catch {
Write-Host "Error while loading supporting PowerShell Scripts"
}
$ServerList = $env:COMPUTERNAME
$SqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server')
$ServerList # add server list
try {
$databases = $SqlServer.Databases
if ($databases.Count -gt 0) {
Write-Host "I am connected"
Backup-Html
} else {
$login = Get-Credential -Message 'Please prov#ide your SQL
Credentials'
$SqlServer.ConnectionContext.LoginSecure = $false
$SqlServer.ConnectionContext.Login = $login.UserName
$SqlServer.ConnectionContext.set_SecurePassword($login.Password)
$SqlServer.ConnectionContext.connect()
}
} catch {
$WarningPreference = 'continue'
Write-Warning "$SqlServer is not contactable"
#$SqlServer.ConnectionContext.Disconnect()
} finally {
$SqlServer.ConnectionContext.Disconnect()
}
Here is the content of Backup-Html
:
$Query = "select top 5 a.server_name, a.database_name,
backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of
backup here
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
ELSE a.[type]
END as BackupType
-- Build a path to the backup
,'\\' +
-- lets extract the server name out of the recorded
server and instance name
CASE
WHEN patindex('%\%',a.server_name) = 0 THEN
a.server_name
ELSE
substring(a.server_name,1,patindex('%\%',a.server_name)-1)
END
-- then get the drive and path and file information
+ '\' + replace(b.physical_device_name,':','$') AS
'\\Server\Drive\backup_path\backup_file'
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily
b
on a.media_set_id = b.media_set_id
where a.database_name Like 'Easy%'
order by a.backup_finish_date desc"
#Use SQLCmd to execute the query on the server
Invoke-Sqlcmd -ServerInstance $SQLServer -Query $Query
Upvotes: 0
Views: 76