Error occurred when running "Set-Location SQLServer:\SQL\127.0.0.1\DEFAULT" in ps1 via Task Scheduler

Question

I can run following commands in PowerShell window with admin privilege:

Set-Location SQLServer:\SQL\127.0.0.1\DEFAULT  
Backup-SqlDatabase -Database "DatabaseName" -BackupContainer "https://StorageAccountName.blob.core.windows.net/ContainerName/" -BackupAction Log  

But if I add them to a .ps1 script and set to run it in Task Scheduler, it didn't work.

I thought it's because that it's running as in Command Prompt, so I tried to run the .ps1 in Command Prompt with admin privilege:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "F:\Scripts\xxx.ps1"  

It report error:

Set-Location : Cannot find drive. A drive with the name 'SQLServer' does not exist.
At F:\Scripts\test.ps1:1 char:1

• Set-Location SQLServer:\SQL\127.0.0.1\DEFAULT
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : ObjectNotFound: (SQLServer:String) [Set-Location], DriveNotFoundException
• FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

I've workaround this by adding -ServerInstance to Backup-SqlDatabase command, but I wondered why the error occurred.

Answer 1

Hi @vinixwu

have you tried to import the SQL Server module?
Sometimes I'm having problems with the $PSModulePath, which can be different for the executing user and its environment, so maybe your script is unable to load the pssql or sqlserver module...

Answer 2

Hi @vinixwu ,

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

This error may mean SQLPS module is not install or not installed correctly

Import -Module SQLPS  

Regards

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.