Backup a StoredProc from sql using PS

[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:

Code: [Select all] [Expand/Collapse]

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

GeSHi © Codebox Plus Extension

please help

[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.