Backup a StoredProc from sql using PS

Ask your Windows 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.
Locked
fthoma15
Posts: 1
Meble kuchenne na zamówienie - na wymiar - Wrocław
Joined: Sun Apr 26, 2020 6:08 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.  
  3. $step2=$step
  4.  
  5. $step3=$step2.Replace('[','')
  6.  
  7. $step4 = $step3.Replace(']','')
  8.  
  9. $step4 = $step4.Split(" ")[1]
  10.  
  11. $step5 = $step4.Split(".")
  12.  
  13. Write-Output  $step5[0,1]
  14.  
  15. [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
  16.  
  17.  
  18.  
  19. $logfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Logs'
  20.  
  21. $bkupfolder = 'C:\Users\fthoma15\Documents\sqlqueries\Backup'
  22.  
  23. $statsfolder = 'C:\Users\fthoma15\Documents\sqlqueries\stats'
  24.  
  25. $SMOserver = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") #-argumentlist $server
  26.  
  27.  
  28.  
  29. $srv = new-Object Microsoft.SqlServer.Management.Smo.Server("$servename")
  30.  
  31. #Prompt for user credentials
  32.  
  33. $srv.ConnectionContext.LoginSecure = $false
  34.  
  35. $credential = Get-Credential
  36.  
  37.  
  38.  
  39. #Deal with the extra backslash character
  40.  
  41. $loginName = $credential.UserName -replace("\\","")
  42.  
  43. #This sets the login name
  44.  
  45. $srv.ConnectionContext.set_Login($loginName);
  46.  
  47.  
  48.  
  49. #This sets the password
  50.  
  51. $srv.ConnectionContext.set_SecurePassword($credential.Password)  
  52.  
  53.  
  54.  
  55. $srv.ConnectionContext.ApplicationName="MySQLAuthenticationPowerShell"  
  56.  
  57.  
  58.  
  59. #$srv.Databases | Select name
  60.  
  61. $db = New-Object Microsoft.SqlServer.Management.Smo.Database
  62.  
  63. $db = $srv.Databases.Item("$dbname")
  64.  
  65. #$db.storedprocedures | Select name
  66.  
  67. $Objects = $db.storedprocedures[$step5[1,0]]
  68.  
  69. #Write-Output  $step5[1,0]
  70.  
  71. #Write-Output $Objects
  72.  
  73. $scripter = new-object ("$SMOserver") $srv
  74.  
  75. $Scripter.Script($Objects) | Out-File $bkupfolder\backup_$($step5[1]).sql
please help

jvierra
Posts: 14368
Joined: Tue May 22, 2007 9:57 am
Contact:

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.

Locked