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