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?

Didn't understand why my question was down voted .

samolpp2
– samolpp2

2016-04-06 15:00:15 +00:00
Commented Apr 6, 2016 at 15:00 — samolpp2
– samolpp2, Commented Apr 6, 2016 at 15:00
Someone probably did not like your code formatting.

Chris Kuperstein
– Chris Kuperstein

2016-04-06 15:28:35 +00:00
Commented Apr 6, 2016 at 15:28 — Chris Kuperstein
– Chris Kuperstein, Commented Apr 6, 2016 at 15:28

Jeffrey · Accepted Answer · 2016-04-06 14:18:56Z

1

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

edited Apr 6, 2016 at 14:18

answered Apr 6, 2016 at 14:13

Jeffrey

4122 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

samolpp2 Over a year ago

I tried, but am getting the same error. am using sql server 2012 SP1 Ent .Edition

samolpp2 Over a year ago

am getting access denied error and am checking , whether i have the necessary rights to run the ps script .Backup-SqlDatabase : System.Data.SqlClient.SqlError: Cannot open backup device 'C:\WSS_Content_2d426b353ded49aa8ace920d4178c298_db_20160406195421.bak'. Operating system error 5(Access is denied.). get-backup-contentdb-powershell.ps1:33 char:1 + Backup-SqlDatabase -ServerInstance $svnm -Database $dbname -BackupFile $bfil + CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], SmoException + FullyQualifiedErrorId : BackupSqlDatabaseCommand

samolpp2 Over a year ago

in my secruity-->folder--> logins-> the server roles for my login are :dbcreator,diskadmin,processadmin,public, securityadmin,serveradmin,setupadmin,sysadmin. i hope these are sufficient for me to run this script. pls advice.

Jeffrey Over a year ago

The SQL Server service can (should) run on a specified user. This user needs to have permissions on the specific folder to write.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related