hey guys,

i am trying to backup and restore database, using SQLPS based on this example, 

https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/

i have installed the MSSQL feature pack which includes the SQLPS, and SQLPSX.

this is my function that loads the SQLPS module, 

# Import SQLPS module
function loadSQLPS
{
    # Test to see if the SQLPS module is loaded, and if not, load it
    write-host "Checking if SQLPS module is imported or not" -ForegroundColor Yellow
    if (-not(Get-Module -name sqlps)){
        Get-Module -ListAvailable | % {
            if ($_.name -eq 'SQLPS') {
                write-host "Importing SQLPS module" -ForegroundColor Yellow            
                Import-Module -Name SQLPS -DisableNameChecking
                write-host "SQLPS imported successfully" -ForegroundColor Green
            }
        }
    } else {
        write-host "SQLPS module is already imported" -ForegroundColor Green
	}
    if (-not(Get-Module -name sqlps)){loadSQLPS}
}

there is problem in there which is causing a loop on "Checking if SQLPS module is imported or not" and it is not loading the SQLPS correctly  so this line of code is not working. 

Backup-SqlDatabase -ServerInstance TESTSQL -Database $dbname -BackupFile

could you please tell me where is the mistake that is causing this issue ? 

thanks,

• Edited by Vagharsh 20 hours 39 minutes ago

Move the calling of the function outside of the function - 

function loadSQLPS
{
    # Test to see if the SQLPS module is loaded, and if not, load it
    write-host "Checking if SQLPS module is imported or not" -ForegroundColor Yellow
    if (-not(Get-Module -name sqlps)){
        Get-Module -ListAvailable | % {
            if ($_.name -eq 'SQLPS') {
                write-host "Importing SQLPS module" -ForegroundColor Yellow            
                Import-Module -Name SQLPS -DisableNameChecking
                write-host "SQLPS imported successfully" -ForegroundColor Green
            }
        }
    } else {
        write-host "SQLPS module is already imported" -ForegroundColor Green
	}
   
}

loadSQLPS

Why use a function.  Just load SQLPS

if(-not (Get-Module sqlps)){Import-Module sqlps}
Backup-SqlDatabase -ServerInstance TESTSQL -Database $dbname -BackupFile

This is all you need.  If you want to test for import failure add this:

Try{
    if(-not (Get-Module sqlps)){Import-Module sqlps -Ea Stop}
    Backup-SqlDatabase -ServerInstance TESTSQL -Database $dbname -BackupFile
}
Catch{
    Throw $_
}