functions/Test-DbaSqlPath.ps1
|
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle# Function Test-DbaSqlPath { <# .SYNOPSIS Tests if file or directory exists from the perspective of the SQL Server service account .DESCRIPTION Uses master.dbo.xp_fileexist to determine if a file or directory exists .PARAMETER SqlInstance The SQL Server you want to run the test on. .PARAMETER Path The Path to tests. Can be a file or directory. .PARAMETER SqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $scred = Get-Credential, then pass $scred object to the -SqlCredential parameter. Windows Authentication will be used if SqlCredential is not specified. SQL Server does not accept Windows credentials being passed as credentials. To connect as a different Windows user, run PowerShell as that user. .PARAMETER Silent Replaces user friendly yellow warnings with bloody red exceptions of doom! Use this if you want the function to throw terminating errors you want to catch. .EXAMPLE Test-DbaSqlPath -SqlInstance sqlcluster -Path L:\MSAS12.MSSQLSERVER\OLAP Tests whether the service account running the "sqlcluster" SQL Server isntance can access L:\MSAS12.MSSQLSERVER\OLAP. Logs into sqlcluster using Windows credentials. .EXAMPLE $credential = Get-Credential Test-DbaSqlPath -SqlInstance sqlcluster -SqlCredential $credential -Path L:\MSAS12.MSSQLSERVER\OLAP Tests whether the service account running the "sqlcluster" SQL Server isntance can access L:\MSAS12.MSSQLSERVER\OLAP. Logs into sqlcluster using SQL authentication. .OUTPUTS System.Boolean .NOTES Author: Chrissy LeMaire (@cl), netnerds.net Requires: Admin access to server (not SQL Services), Remoting must be enabled and accessible if $SqlInstance is not local dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com) Copyright (C) 2016 Chrissy LeMaire This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>. .LINK https://dbatools.io/Test-DbaSqlPath #> [CmdletBinding()] [OutputType([bool])] param ( [Parameter(Mandatory = $true)] [Alias("ServerInstance", "SqlServer")] [DbaInstanceParameter] $SqlInstance, [Parameter(Mandatory = $true)] [string] $Path, [System.Management.Automation.PSCredential] $SqlCredential, [switch] $Silent ) try { $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential } catch { Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue return } Write-Message -Level VeryVerbose -Message "Path check is $path" $sql = "EXEC master.dbo.xp_fileexist '$path'" try { Write-Message -Level Debug -Message "Executing: $sql" $fileexist = $server.ConnectionContext.ExecuteWithResults($sql) } catch { Stop-Function -Message "Failed to test the path $Path" -ErrorRecord $_ -Target $SqlInstance return } if ($fileexist.tables.rows[0] -eq $true -or $fileexist.tables.rows[1] -eq $true) { return $true } else { return $false } Test-DbaDeprecation -DeprecatedOn "1.0.0" -Silent:$false -Alias Test-SqlPath } |