Error thrown while taking backup of SQL Server content database using PowerShell

Question

I am trying to take a backup of SQL Server content database using PowerShell.

Here is my code:

$dt = Get-Date -Format yyyyMMddHHmmss
$null = 
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
   $srvrvinstancename= "srvr-04\mssqlserver1"
    $svr = New-Object 
      Microsoft.SqlServer.Management.Smo.Server($srvrvinstancename);
       #Write-Output $svr WSS_Content_2d426b353ded49aa8ace920d4178c298
       $bdir = "C:"
        $svnm = $svr.Name
       $db = 
       $svr.Databases['WSS_Content_2d426b353ded49aa8ace920d4178c298']
       $dbname = $db.Name
      $dt = get-date -format yyyyMMddHHmmss
        $bfil = "$bdir\$($dbname)_db_$($dt).bak"
       Backup-SqlDatabase -ServerInstance $svnm -Database $dbname -
       BackupFile $bfil

but when I run the code I get the error:

Backup-SqlDatabase : The 'Backup-SqlDatabase' command was found in the module 'SQLPS', but the module could not be loaded. For more information, run 'Import-Module SQLPS'.
At line:19 char:1
+ Backup-SqlDatabase -ServerInstance $svnm -Database $dbname -BackupFile $bfil + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Backup-SqlDatabase:String) [], CommandNotFoundException + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

After that, I ran

import-module sqlps

But this also ran into error state:

Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\110\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 http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module SQLPS
+ ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

What am I doing wrong?

Jeffrey Jeffrey · Accepted Answer · 2016-04-06T14:13:29

I think you need to load a couple off Assembly:

# Load SQL server assemblies
#
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")         | Out-Null

# SmoExtended 
#
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")     | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")            | Out-Null

UPDATE.

For scripts which can't be executed you need to do (as administrator in Powershell)

Set-ExecutionPolicy remotesigned

Error thrown while taking backup of SQL Server content database using PowerShell

1 Answers