Issue with the code need help

This topic contains 9 replies, has 3 voices, and was last updated by  Sam Boutros 11 months, 2 weeks ago.

  • Author
    Posts
  • #55105

    Windows LiveUser175
    Participant

    I have the below mentioned code. I pass the database name as a parameter.
    Param
    (
    [Parameter(Mandatory = $true)] [String] $dbname
    )
    Import-Module SQLPS -DisableNameChecking
    $networkshare = '\\Domain\Test'
    $dbfilebackup = $networkshare + $DBName + '.bak'
    $dblogbackup = $networkshare + $DBName + '.trn'
    $sqlaogroup = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"
    $sqlprimary = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"
    $sqlsecondary = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"
    $sqlstringprimary = 'SQLSERVER:\SQL\' + $sqlprimary + '\DEFAULT\AvailabilityGroups\' + $sqlaogroup
    $sqlstringsecondary = 'SQLSERVER:\SQL\' + $sqlsecondary + '\DEFAULT\AvailabilityGroups\' + $sqlaogroup
    Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlprimary
    Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $sqlsecondary -NoRecovery
    Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlprimary -BackupAction Log
    Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $sqlsecondary -RestoreAction Log -NoRecovery
    Add-SqlAvailabilityDatabase -Path $sqlstringprimary -Database $DBName
    Add-SqlAvailabilityDatabase -Path $sqlstringsecondary -Database $DBName

    I am getting the below mentioned error message. Can you help me please in fixing the code.

    I believe $sqlstringprimary and $sqlstringsecondary is not getting loaded properly because of $sqlprimary and $sqlsecondary

    PS D:\> .\Test.ps1 Test3
    Backup-SqlDatabase : Failed to connect to server System.Data.DataRow.
    At D:\Test.ps1:14 char:1
    + Backup-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance $ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Backup-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

    Restore-SqlDatabase : Failed to connect to server System.Data.DataRow.
    At D:\Test.ps1:15 char:1
    + Restore-SqlDatabase -Database $DBName -BackupFile $dbfilebackup -ServerInstance ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Restore-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

    Backup-SqlDatabase : Failed to connect to server System.Data.DataRow.
    At D:\Test.ps1:16 char:1
    + Backup-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $s ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Backup-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand

    Restore-SqlDatabase : Failed to connect to server System.Data.DataRow.
    At D:\Test.ps1:17 char:1
    + Restore-SqlDatabase -Database $DBName -BackupFile $dblogbackup -ServerInstance $ ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Restore-SqlDatabase], ConnectionFailureException
    + FullyQualifiedErrorId : Microsoft.SqlServer.Management.Common.ConnectionFailureException,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

  • #55109

    Allen Ryerson
    Participant

    Hello!

    Whats your end goal? Are you just whipping something up to backup your SQL database?

  • #55114

    Windows LiveUser175
    Participant

    I need to add my database dynamically to the code by passing it on a parameter. If i hard cord it it works fine. When I make the code as dynamic it is failing . That is my final goal. I wrote the code with the logic. Syntax is wrong in this for the dynamic code.

    • #55116

      Allen Ryerson
      Participant

      Ah I see where you are getting at. I have a script that backups my production SQL databases. I am not in a position to test at the moment but I modified that script into a function (sorry the formatting is sloppy). Let me know if this works for you, I'll test if I don't hear back from you on Monday

      Function Backup-SQL {

      [CmdletBinding()]
      param
      (
      [parameter(position=3)]
      $Instance,
      [parameter(position=2)]
      $Database,
      [Parameter(position=1)]
      $BackupPath,
      [Parameter(position=0)]
      $Logfile
      )

      $timestamp = Get-Date -format yyyy-MM-dd
      $debugPreference = "Continue"

      Start-Transcript -Path $logfile

      Write-verbose "Beginning the SQL database backup procedure"

      Try
      {
      Backup-SqlDatabase -ServerInstance $instance -Database $Database -BackupFile $BackupPath -ErrorAction Stop

      }

      Catch
      {
      Write-Error "Something went wrong! Check the logfile to see what happened"
      }

      }

  • #55115

    Sam Boutros
    Participant
  • #55117

    Windows LiveUser175
    Participant

    I need to get this statement working

    First the statements $sqlaogroup , $sqlprimary and $sqlsecondary needs to be built.

    Then $sqlstringprimary and $sqlstringsecondary needs to be loaded. Once it is done i need to backup and restore the databases and then add it to the availability group.

    Backup of the databases is not my goal.

    The script which you had given me is not helping in going ahead to acheive my goal.

    Thank You for giving me the reference link.

  • #55118

    Sam Boutros
    Participant

    These 3 lines

    $sqlaogroup = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"
    $sqlprimary = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"
    $sqlsecondary = Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;"

    return a PS object. If you just need the server name use:

    $sqlaogroup = (Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;").column1
    $sqlprimary = (Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;").column1
    $sqlsecondary = (Invoke-Sqlcmd -ServerInstance 'TestSQL,1433' -Database 'TestDB' -Query "SELECT @@servername;").column1

  • #55119

    Windows LiveUser175
    Participant

    I got the code working. It is good now.

  • #55120

    Windows LiveUser175
    Participant

    Thank You for helping me.

  • #55121

    Sam Boutros
    Participant

    You're welcome

You must be logged in to reply to this topic.