Backup sql Storedproc with powershell

Welcome Forums General PowerShell Q&A Backup sql Storedproc with powershell

Viewing 4 reply threads
  • Author
    Posts
    • #222702
      Participant
      Topics: 1
      Replies: 2
      Points: -5
      Rank: Member

      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 
      • This topic was modified 4 weeks, 1 day ago by fthoma1515.
      • This topic was modified 4 weeks, 1 day ago by fthoma1515.
    • #222882
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      If you un-remark the Write-Output lines, what is it showing? All you can do is step through the code with output or debug to see where it’s failing.

    • #222897
      Participant
      Topics: 1
      Replies: 2
      Points: -5
      Rank: Member

      I uncommented write-output $objects but its showing blank, not sure if I am supposed to put the sp in a different format. I remove the get-credential part and it produces the file with the sp, but as soon as I put the credential part its giving me blank file.

    • #222900
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      The user\password is probably SQL login, not Windows. Look at Password here, it indicates:

      Gets or sets the password used with the logon to establish a connection with the server when SQL Server Authentication is used

      You may have to execute the script as different Window credentials if you want to use different Windows context. This is basically building a connection string which uses SQL login or inherited security (Windows).

    • #222948
      Participant
      Topics: 1
      Replies: 2
      Points: -5
      Rank: Member

      Thanks Rob I think I figured this out, it was a permission issue with the sql account. I gave the access and it started working.

Viewing 4 reply threads
  • You must be logged in to reply to this topic.