Backup a StoredProc from sql using PS

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 3 years and 11 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
fthoma15
Posts: 1
Last visit: Sun Apr 26, 2020 6:46 pm

Backup a StoredProc from sql using PS

Post by fthoma15 »

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:
  1. param([String]$step='exeC dbo.test',[String]$sqlfile='',[String]$servename = 'test',[String]$dbname = 'test')
  2. $step2=$step
  3. $step3=$step2.Replace('[','')
  4. $step4 = $step3.Replace(']','')
  5. $step4 = $step4.Split(" ")[1]
  6. $step5 = $step4.Split(".")
  7. Write-Output  $step5[0,1]
  8. [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
  9.  
  10. $logfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Logs'
  11. $bkupfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Backup'
  12. $statsfolder = 'C:\Users\fthoma15\Documents\sqlqueries\stats'
  13. $SMOserver = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #-argumentlist $server
  14.  
  15. $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("$servename")
  16. #Prompt for user credentials
  17. $srv.ConnectionContext.LoginSecure = $false
  18. $credential = Get-Credential
  19.  
  20. #Deal with the extra backslash character
  21. $loginName = $credential.UserName -replace("\\","")
  22. #This sets the login name
  23. $srv.ConnectionContext.set_Login($loginName);
  24.  
  25. #This sets the password
  26. $srv.ConnectionContext.set_SecurePassword($credential.Password)  
  27.  
  28. $srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  
  29.  
  30. #$srv.Databases | Select name
  31. $db = New-Object Microsoft.SqlServer.Management.Smo.Database
  32. $db = $srv.Databases.Item("$dbname")
  33. #$db.storedprocedures | Select name
  34. $Objects = $db.storedprocedures[$step5[1,0]]
  35. #Write-Output  $step5[1,0]
  36. #Write-Output $Objects
  37. $scripter = new-object ("$SMOserver") $srv
  38. $Scripter.Script($Objects) | Out-File $bkupfolder\backup_$($step5[1]).sql
please help
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Backup a StoredProc from sql using PS

Post by jvierra »

The following is all you need to extract a stored procedure.

Code: Select all

$servername = 'your server instance name'
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$srv = [Microsoft.SqlServer.Management.Smo.Server]::new($servername)
<#  If using SQlAuthentication add teh following
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Login = 'username'
$srv.ConnectionContext.Password = 'password'
#>
$db = $srv.Databases['Northwind']
$db.StoredProcedures['CustOrderHist'].Script()
Just output the last line to a file and you are set.
This topic is 3 years and 11 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked