Backup sql Storedproc with powershell

PowerShell Help
1

I am trying to create a backup of a SQL stored procedure using PowerShell, but it produces a blank file. It’s not throwing an error.

Here is my code:

param([String]$step='exeC dbo.test',[String]$sqlfile='',[String]$servename = 'test',[String]$dbname = 'test')
$step2=$step
$step3=$step2.Replace('[','')
$step4 = $step3.Replace(']','')
$step4 = $step4.Split(" ")[1]
$step5 = $step4.Split(".")
Write-Output  $step5[0,1]
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null

$logfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Logs'
$bkupfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Backup'
$statsfolder = 'C:\Users\fthoma15\Documents\sqlqueries\stats'
$SMOserver = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #-argumentlist $server 

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("$servename")
#Prompt for user credentials
$srv.ConnectionContext.LoginSecure = $false
$credential = Get-Credential 

#Deal with the extra backslash character
$loginName = $credential.UserName -replace("\\","") 
#This sets the login name 
$srv.ConnectionContext.set_Login($loginName); 

#This sets the password 
$srv.ConnectionContext.set_SecurePassword($credential.Password)  

$srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  

#$srv.Databases | Select name
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("$dbname") 
#$db.storedprocedures | Select name
$Objects = $db.storedprocedures[$step5[1,0]]
#Write-Output  $step5[1,0]
#Write-Output $Objects
$scripter = new-object ("$SMOserver") $srv 
$Scripter.Script($Objects) | Out-File $bkupfolder\backup_$($step5[1]).sql
2

If you un-remark the Write-Output lines, what is it showing? All you can do is step through the code with output or debug to see where it’s failing.

3

I uncommented write-output $objects but its showing blank, not sure if I am supposed to put the sp in a different format. I remove the get-credential part and it produces the file with the sp, but as soon as I put the credential part its giving me blank file.

4

The user\password is probably SQL login, not Windows. Look at Password here, it indicates:

Gets or sets the password used with the logon to establish a connection with the server when SQL Server Authentication is used

You may have to execute the script as different Window credentials if you want to use different Windows context. This is basically building a connection string which uses SQL login or inherited security (Windows).

5

Thanks Rob I think I figured this out, it was a permission issue with the sql account. I gave the access and it started working.