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