Author Posts

October 8, 2016 at 8:42 am

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

October 8, 2016 at 2:16 pm

Hello!

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

October 8, 2016 at 4:39 pm

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.

October 8, 2016 at 6:11 pm

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

}

October 8, 2016 at 6:46 pm

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.

October 8, 2016 at 8:29 pm

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

October 8, 2016 at 9:33 pm

I got the code working. It is good now.

October 9, 2016 at 7:19 am

Thank You for helping me.