Backup-SqlDatabase is not recognized

Question

I have a PowerShell script to backup a database. But today it has stoped working with next error:

Backup-SqlDatabase : The term 'Backup-SqlDatabase' is not recognized as 
the name of a cmdlet, function, script file,
or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and
try again.

I didn't change the script. What could be the reason for that?

UPDATE: Installed SqlServer module. Now I have next:

Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

PS C:\Temp> import-module sqlserver -erroraction stop -verbose
VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\sqlserver.psd1'.
VERBOSE: Loading 'TypesToProcess' from path 'C:\Program
Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\sqlprovider.types.ps1xml'.
VERBOSE: Loading 'FormatsToProcess' from path 'C:\Program
Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\sqlprovider.format.ps1xml'.
VERBOSE: Populating RepositorySourceLocation property for module sqlserver.
VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\SqlServer.psm1'.
VERBOSE: Exporting function 'SQLSERVER:'.
VERBOSE: Exporting alias 'Encode-SqlName'.
VERBOSE: Exporting alias 'Decode-SqlName'.
VERBOSE: Importing function 'SQLSERVER:'.
VERBOSE: Importing alias 'Decode-SqlName'.
VERBOSE: Importing alias 'Encode-SqlName'.
PS C:\Temp> Get-Command -Name Backup-SqlDatabase
Get-Command : The term 'Backup-SqlDatabase' is not recognized as the name of a cmdlet, function, script file, or
operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At line:1 char:1
+ Get-Command -Name Backup-SqlDatabase
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Backup-SqlDatabase:String) [Get-Command], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException,Microsoft.PowerShell.Commands.GetCommandCommand

UPDATE 2: Uninstalled SqlServer module, directory were removed. After that reinstalled it. Installation log:

PS C:\WINDOWS\system32> Install-Module -Name SqlServer -Repository PSGallery -Verbose
VERBOSE: Repository details, Name = 'PSGallery', Location = 'https://www.powershellgallery.com/api/v2/'; IsTrusted
 = 'False'; IsRegistered = 'True'.
VERBOSE: Using the provider 'PowerShellGet' for searching packages.
VERBOSE: Using the specified source names : 'PSGallery'.
VERBOSE: Getting the provider object for the PackageManagement Provider 'NuGet'.
VERBOSE: The specified Location is 'https://www.powershellgallery.com/api/v2/' and PackageManagementProvider is
'NuGet'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='SqlServer'' for ''.
VERBOSE: Total package yield:'1' for the specified package 'SqlServer'.
VERBOSE: Performing the operation "Install-Module" on target "Version '21.0.17224' of module 'SqlServer'".

Untrusted repository
You are installing the modules from an untrusted repository. If you trust this repository, change its
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from
 'PSGallery'?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y
VERBOSE: The installation scope is specified to be 'AllUsers'.
VERBOSE: The specified module will be installed in 'C:\Program Files\WindowsPowerShell\Modules'.
VERBOSE: The specified Location is 'NuGet' and PackageManagementProvider is 'NuGet'.
VERBOSE: Downloading module 'SqlServer' with version '21.0.17224' from the repository
'https://www.powershellgallery.com/api/v2/'.
VERBOSE: Searching repository 'https://www.powershellgallery.com/api/v2/FindPackagesById()?id='SqlServer'' for ''.
VERBOSE: InstallPackage' - name='SqlServer',
version='21.0.17224',destination='C:\Users\Oleg\AppData\Local\Temp\1981035148'
VERBOSE: DownloadPackage' - name='SqlServer',
version='21.0.17224',destination='C:\Users\Oleg\AppData\Local\Temp\1981035148\SqlServer\SqlServer.nupkg',
uri='https://www.powershellgallery.com/api/v2/package/SqlServer/21.0.17224'
VERBOSE: Downloading 'https://www.powershellgallery.com/api/v2/package/SqlServer/21.0.17224'.
VERBOSE: Completed downloading 'https://www.powershellgallery.com/api/v2/package/SqlServer/21.0.17224'.
VERBOSE: Completed downloading 'SqlServer'.
VERBOSE: Hash for package 'SqlServer' does not match hash provided from the server.
VERBOSE: InstallPackageLocal' - name='SqlServer',
version='21.0.17224',destination='C:\Users\Oleg\AppData\Local\Temp\1981035148'
VERBOSE: Catalog file 'SqlServer.cat' is not found in the contents of the module 'SqlServer' being installed.
VERBOSE: Valid authenticode signature found in the file 'SqlServer.psd1' for the module 'SqlServer'.
VERBOSE: Module 'SqlServer' was installed successfully to path 'C:\Program
Files\WindowsPowerShell\Modules\SqlServer\21.0.17224'.

Even after that Import-Module SqlServer -ErrorAction Stop -Verbose hasn't changed and Backup-SqlDatabase is still is not available. What could be the reason?

Which are your versions? Operating system, PowerShell and SQL Server. — G42, Mar 19 '18 at 09:59

score 6 · Answer 1 · answered Mar 19 '18 at 11:27

6

That function is provided by the sqlps (old & busted) and sqlserver (current) modules. sqlps and older versions of sqlserver were provided by the SQL Server Management Studio installation, but sqlserver is now in the PowerShell Gallery. Assuming you have a current version of PowerShell/Windows Management Framework, you can install-module sqlserver (run in an Administrator PowerShell session) and get the latest version installed globally.

As to what happened to your script:

Possibility #1: You're using a very old version of PowerShell which doesn't auto-load modules and you're not explicitly importing the sqlserver or sqlps module into the session/script where you're calling this function. Solution: upgrade to a current release of PowerShell which does support auto-loading modules and/or explicitly import the proper module into your script/session with import-module.

Possibility #2: Someone uninstalled or moved the module that you're getting that function from, or it's not in your module search path. Solution: Check $PSModulePath and then look in each location for the module. Easiest would be to reinstall it in a global scope.

answered Mar 19 '18 at 11:27

alroc

27,574
6
51
97

Thank you for replay. I'm using PS 5.1.16299. I have installed SqlServer as described in https://learn.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module, I'm explicitly importing SqlServer module (without any errors) but 'Backup-SqlDatabase' is still not recognized. What could be the reason now? Could sqlps conflict with SqlServer module? – Oleg Oshkoderov Mar 19 '18 at 11:37
You can have the two installed at the same time, you just might need to do extra work to run one vs. the other. What do you get from `import-module sqlserver -erroraction stop -verbose` and `get-command -name backup-sqldatabase`? – alroc Mar 19 '18 at 13:22
`Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\sqlserver.psd1'. Loading 'TypesToProcess' from path '...\sqlprovider.types.ps1xml'. Loading 'FormatsToProcess' from path '...\sqlprovider.format.ps1xml'. Populating RepositorySourceLocation property for module sqlserver. Loading module from path '...\SqlServer.psm1'. Exporting function 'SQLSERVER:'. Exporting alias 'Encode-SqlName'. Exporting alias 'Decode-SqlName'. Importing function 'SQLSERVER:'. Importing alias 'Decode-SqlName'. Importing alias 'Encode-SqlName'.` – Oleg Oshkoderov Mar 19 '18 at 13:33
`...` is - C:\Program Files\WindowsPowerShell\Modules\sqlserver\21.0.17224\ – Oleg Oshkoderov Mar 19 '18 at 13:33
You can't cram the full output into a comment. Any errors at all? Put them in your original post – alroc Mar 19 '18 at 13:37
No error during import, but command is still not recognized (See update in the question). Also it looks like import only imports 2 aliases and 1 function. Is it normal? – Oleg Oshkoderov Mar 19 '18 at 14:12
@OlegOshkoderov No, your output is definitely not normal and doesn't look like mine, even with the same version. I'd suggest removing it via `remove-module`, then delete the files, then reinstall from the gallery. – alroc Mar 19 '18 at 14:21
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167103/discussion-between-oleg-oshkoderov-and-alroc). – Oleg Oshkoderov Mar 19 '18 at 15:35

score 0 · Answer 2 · answered Mar 19 '18 at 09:55

0

You need to import the SQL Server PowerShell module to be able to access the cmdlets it contains:

Import-Module SQLPS -ErrorAction Stop

Run this code to see if the function is available to you or not:

Get-Command -Name Backup-SqlDatabase

Here are the results from my machine:

CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Backup-SqlDatabase                                 14.0       SQLPS

answered Mar 19 '18 at 09:55

gvee

16,732
35
50

When I Run Import-Module command it fails with next message: ````Set-Location : Cannot find drive. A drive with the name 'SQLSERVER' does not exist. At C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\SqlPsPostScript.ps1:1 char:1 + Set-Location SQLSERVER: + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (SQLSERVER:String) [Set-Location], DriveNotFoundException + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocationCommand ```` – Oleg Oshkoderov Mar 19 '18 at 10:34
@OlegOshkoderov So basically the module cannot be imported due to an error; which is why the cmdlet is unavailable to you. Try re-install the module. – gvee Mar 19 '18 at 10:35
`sqlps` is dead and has been superseded by `sqlserver` (which is available in the PS Gallery). Please don't encourage people to use that old, busted module. – alroc Mar 19 '18 at 11:21
I have installed `sqlserver` module, and imported it, but `Backup-SqlDatabase` is still not recognized. What could be the reason? – Oleg Oshkoderov Mar 19 '18 at 11:26

score 0 · Answer 3 · edited May 11 '21 at 12:47

I too have the same issue but with Delete-SqlDatabase from SQLPS.

In my case I was trying to call a function Delete-SqlDatabase which I declared and consuming in my code.

The mistake I did is to call the function which was down below.

You see in Powershell in order for a function to be visible, you have to declare it on top. The main function should be the last section hierarchy wise.

Its such a silly thing. I am sure you figured this out within a day.

I am 100% certain this is your issue. I know this thread is too old but it might help other's like me who can potentially save an hours time.

Backup-SqlDatabase is not recognized

3 Answers3

Linked