Issue with the code need help

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

Hello!

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

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.

Try this script https://gallery.technet.microsoft.com/Powershell-script-to-12d1a11f
Documentation is in this post https://superwidgets.wordpress.com/2016/06/05/powershell-script-to-backup-all-user-databases-on-current-sql-server/

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”
}

}

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.

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

I got the code working. It is good now.

Thank You for helping me.

You’re welcome