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
Post
by fthoma15 » Sun Apr 26, 2020 6:14 pm
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
please help
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times
Post
by jvierra » Sun Apr 26, 2020 7:04 pm
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