SQL Server 2012 Import-Module 'sqlps' breaks the "Test-Path" PowerShell cmdlet - by mikea730

Status : 

  External<br /><br />
		This item may be valid but belongs to an external system out of the direct control of this product team.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 768743 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/25/2012 12:24:12 PM
Access Restriction Public


I've run into something that is "very" frustrating with the new SQL Server 2012 PowerShell module.  When I Import the module, it breaks the "Test-Path" cmdlet when trying to test a UNC path to a directory.

For example:   

"Test-Path -path \\server\dirname" returns true as expected before the sqlps module is imported.  But after you import the SQL Server module "Import-Module 'sqlps' –DisableNameChecking" the same Test-Path now returns false.

If I run the following in Windows PowerShell ISE I see the following results:

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
Test-Path -path "\\server\directoryname"


UPDATE: after more testing, it looks like the problem happens with any cmdlet that references a UNC.  The New-Item has the same problem.  Before importing 'sqlps', New-Item is able to create a directory at the UNC path specified, but ater importing 'sqlps', the New-Item fails.

Sign in to post a comment.
Posted by Diane Sithoo on 7/2/2015 at 6:31 PM
Neither Set-Location or zjo's comments about using Microsoft.PowerShell.Core\FileSystem::\\server\directoryname instead resolved this issue for me in SQL 2014. Instead my work-around was to use "CD <working_directory_path>" after the Import-Module statement:

#Set working patch to wherever you are calling the script from
$workingDirectoryPath = "C:\PowerShell"

Import-Module SQLPS -DisableNameChecking

#Now change directory to your working path
cd $workingDirectoryPath

#This returns true now.
Test-Path -path "\\server\directoryname"
Posted by Indrajit Bose on 2/20/2015 at 6:26 AM
Another important point, I found the following error for test path:

Test-Path -path "\\<ServerIP>\c$\MSSQL_BACKUP"
Import-Module 'sqlps' –DisableNameChecking
Test-Path -path "\\<ServerIP>\c$\MSSQL_BACKUP"

PS C:\Users\Administrator> Import-Module 'sqlps' –DisableNameChecking
Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\sqlps\Sqlps.ps1 cannot
be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at
At line:1 char:1
+ Import-Module 'sqlps' –DisableNameChecking
Posted by Indrajit Bose on 2/20/2015 at 6:19 AM
Hi I tried the workaround but still it didn't work, following is the code which ran successfully from the PowerShell console but not from the SQL Server Agent. The owner of the job is "NT Service\SQLSERVERAGENT", and this id has been added in the designated folders with full permission. Please suggest what to do.

Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Copy-Item -literalpath "FileSystem::\\<ServerIP>\c$\MSSQL_BACKUP\ABC_FullDBBkup_1.bak" -Destination "\\<ServerIP>\c$\MSSQL_BACKUP\New"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access is denied Access is denied ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Copy-Item -literalpath "FileSystem::\\<ServerIP>\c$\MSSQL_BACKUP\ABC_FullDBBkup_1.bak" -Destination "\\<ServerIP>\c$\MSSQL_BACKUP\New"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\<ServerIP>\c$\MSSQL_BACKUP\ABC_FullDBBkup_1.bak' because it does not exist. '. Process Exit Code 0. The step succeeded.

Posted by mikea730 on 7/25/2014 at 8:10 AM
Thanks, yes, I've been using zjo's workaround by specifying the "C:" drive.
Posted by kstuber on 7/25/2014 at 5:53 AM
A note that if you run into this issue in the SQL 2014 Agent, the workaround will not resolve the issue, but zjo's advice (i.e. specifying Microsoft.PowerShell.Core\FileSystem:: ) will work.
Posted by Sethu Srinivasan on 11/3/2012 at 2:32 PM
Posted a workaround
test-path -literalpath "FileSystem::\\machine\uncpath"

Sethu Srinivasan [MSFT]
SQL Server
Posted by Sethu Srinivasan on 11/3/2012 at 2:26 PM
I see the same issue with other Powershell providers as well ( Example: Registry Provider ). This issue may not be SQL Powershell specific issue

I ran the following commands in Windows Powershell 2.0 and Windows powershell 3.0

PS C:\Users> New-PSDrive -PSProvider registry -Root HKEY_CLASSES_ROOT -Name HKCR

set-location HKCR:

PS HKCR:\> test-path \\machine\share


PS HKCR:\> c:
PS C:\Users> test-path \\machine\share
PS C:\Users>


Sethu Srinivasan [MSFT]

SQL Server

Posted by zjo on 11/3/2012 at 1:51 PM
After importing the 'sqlps' module, your location is changed to the SQLSERVER drive, as indicated by the powershell prompt:


This drive is an instance of the SQLSERVER Powershell Provider.

The core powershell cmdlets (Clear-Item, Convert-Path, Copy-Item, Get-Item, Get-Location, Invoke-Item, Join-Path, Move-Item, New-Item, Pop-Location, Push-Location, Remove-Item, Rename-Item, Resolve-Path, Set-Item, Set-Location, Split-Path, Test-Path, ...) will assume the item, path or location you specify as an argument of your cmdlet is relative to the Powershell Provider you are currently using.

The UNC path is meaningful to the FileSystem Provider, but not to the SQLServer Provider (which you are currently using).

To resolve your issue, you could (1) explicitly specify the FileSystem provider in your path or (2) switch to a drive of providertype FileSystem before invoking the Test-Path cmdlet for the second time:

Solution 1:

Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
test-path -path Microsoft.PowerShell.Core\FileSystem::\\server\directoryname
Solution 2:
Test-Path -path "\\server\directoryname"
Import-Module 'sqlps' –DisableNameChecking
Test-Path -path "\\server\directoryname"