1

I'm trying to automate a script that backups a database in one environment, copies it to the other, then restores it there. But there's some strange behavior going on.

First, I try to access a network location. Works like a bliss:

C:\> Get-ChildItem \\remote-server\e$

Returns

Directory: \\remote-server\e$
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----         19-3-2015     11:49            Program Files

However, as soon as I import the Sqlps module (which is the collection of SQL Server Management Objects - SMO) for interacting with the database, the networked locations can't be found anymore:

PS C:\> Import-Module Sqlps -DisableNameChecking;
PS SQLSERVER:\> Get-ChildItem \\remote-server\e$

Returns:

Get-ChildItem : Cannot find path '\\remote-server\e$' because it does not exist.
At line:1 char:1
+ Get-ChildItem \\remote-server\e$
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (\\remote-server\e$:String)[Get-ChildItem], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetChildItemCommand

Accessing regular file locations is no problem and just works.

How can I access network locations after having loaded the SQLPS module / SMO?

2 Answers 2

2

When you run the Import-Module Sqlps -DisableNameChecking; command, your current location is immediately changed to PS SQLSERVER:\>. Being "in" SQLSERVER prevents you from using Get-ChildItem related to a drive location.

First, you will need to change your current location back to a drive location with Set-Location or cd. Then, you will be able to use Get-ChildItem for the folder, then navigate back to SQLSERVER:\ (if necessary):

Example:

PS C:\ > Import-Module Sqlps -DisableNameChecking;
PS SQLSERVER:\> Set-Location C:\
PS C:\> Get-ChildItem \\remote-server\e$
PS C:\> Set-Location SQLSERVER:\

I should also note that the behavior of changing your location on importing the module is a bug that was fixed in SQL Server 2016. In the future, your current location would not be switched to SQLSERVER:\, so you would not need to Set-Location back to a drive location.

0

AS @AMtwo already mentioned, the reason is you're "in" SQL Server, where the Get-ChildItem, but also other concepts like Move-Item apply to SQL Server object (as opposed to filesystem-objects).

To force the cmdlets to use the locations you provided as filesystem-locations, you could prepend the path with Microsoft.PowerShell.Core\FileSystem:: like this:

Get-ChildItem "Microsoft.PowerShell.Core\FileSystem::\\remote-server\e$"

Now, PowerShell tries to access the path like a filesystem-path, and the network-path is available again.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .