Code: Select all
function Start-SavantDevCloneRefresh {
[CmdletBinding()]
Param(
[parameter(Mandatory = $True)]
[ValidateSet("DCSTL", "DCCAR", "DCFRN", "DCTEX", "DCOSW", "DCPEN", "DCMES", "DCPET")]
[String]$SourceSiteName,
[parameter(Mandatory = $True)]
[ValidateSet("REDACTED", "REDACTED")]
[String]$TargetServerFQDN,
[parameter(Mandatory = $true)]
[boolean]$PreviousCloneExists
)
Process {
#declare function variables
$str_VeeamBackupName = $SourceSiteName + ">EXAGRID SQL to HQ Backup Copy"
$str_VeeamServer = "REDACTED"
$str_backupID = $null
$str_TargetServer = $TargetServerFQDN
$str_sourceServer = $null
$str_DBName = "Savant"
$obj_VBRSQLdatabase = $null
$obj_GuestCreds = $null
$obj_connectiontest = $null
$obj_restorepoint = $null
$path = @("D:\CloneDatabases\DataFiles\Savant.mdf","D:\CloneDatabases\LogFiles\Savant.ldf")
#set source server based on text from Site Name param
IF ($SourceSiteName -eq "DCSTL") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCTEX") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCCAR") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCOSW") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCFRN") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCPEN") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCMES") {$str_sourceServer = "REDACTED"}
IF ($SourceSiteName -eq "DCPET") {$str_sourceServer = "REDACTED"}
#define the SQL to run which executes a stored procedure
$SQL_SANITIZE_SP = "exec dbo.SP_SavantSensitiveDataCleanup"
#define the SQL to fix logins on Savant database
$SQL_POSTPROC_SP = "
USE [Savant]
GO
EXEC sUT.sCloneDBPostProcessMaster
"
#define the SQL to set Savant to RESTRICTED_USER mode prior to sanitizing
$SQL_SET_RESTRICTED_USER = "
USE [master]
GO
ALTER DATABASE [Savant] SET RESTRICTED_USER
GO"
#define SQL statements for setting single user and detaching database
$SQL_set_single_user = "ALTER DATABASE $str_DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
$SQL_drop_database = "DROP DATABASE $str_DBName"
#If old copy exists, set old copy to single user and drop rather than detach
IF ($PreviousCloneExists -eq $true) {
Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_set_single_user -QueryTimeout 300
Start-Sleep -Seconds 30
Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_drop_database -QueryTimeout 300
}
#connect to Veeam Server if not connected
$obj_connectiontest = Get-VBRServerSession
IF ($obj_connectiontest.Server -eq "DMHQ-VEEAM1.dminet.com") {Continue}
ELSE {Connect-VBRServer -Server $str_VeeamServer -Port 9392 }
#Get the ID guid of the most recent backup of the specified server
#Where-Object {$_.CreationTime -gt (get-date).addDays(-1)} |
#
$str_backupID = Get-VBRBackup -Name $str_VeeamBackupName | Get-VBRRestorePoint | Where-Object {$_.IsConsistent -eq $true} | Sort-Object CreationTime -Descending | Select-Object -First 1 | ForEach-Object {$_.Id}
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "$str_sourceServer" | Sort -Property CreationTime -Descending | Select-Object -First 1
$obj_restorepoint = Start-VESQLRestoreSession -RestorePoint $restorepoint | Where-Object {$_.Id -eq $str_backupID.Guid}
#Get the Veeam credential object needed to restore
$obj_GuestCreds = Get-VBRCredentials -name "REDACTED"
#Get the databases in the specified backup and choose Savant
$obj_VBRSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name "Savant"
#Restore the database to alternate location - DCTEST-SQL1
Restore-VESQLDatabase -Database $obj_VBRSQLdatabase -ServerName $str_TargetServer -DatabaseName $str_DBName -TargetPath $path -GuestCredentials $obj_GuestCreds -SqlCredentials $obj_GuestCreds -Wait
#disconnect from the Veeam Backup Server
Disconnect-VBRServer
#slight delay after restore before invoking stored procedure
Start-Sleep -seconds 30
#set the database to RESTRICTED_USER mode until it is sanitized
Invoke-Sqlcmd -ServerInstance $str_TargetServer -Query $SQL_SET_RESTRICTED_USER -QueryTimeout 300
#Execute the stored procedure to sanitize the database once mounted
Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database master -query $SQL_SANITIZE_SP -QueryTimeout 300
#Execute the SQL to fix logins
Invoke-Sqlcmd -ServerInstance $str_TargetServer -Database $str_DBName -Query $SQL_POSTPROC_SP -QueryTimeout 1000
#wait, then change back to C drive, and remove imported module from session
Start-Sleep -seconds 15
C:
Get-VESQLDatabase : Cannot bind parameter 'Session'. Cannot convert the "" value of type
"System.String" to type "Veeam.SQL.PowerShell.VESQLRestoreSession".
At C:\Windows\system32\WindowsPowerShell\v1.0\Modules\DMDevCloneOps2\DMDevCloneOps2.psm1:1374
char:58
+ ... RSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name " ...
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-VESQLDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Veeam.SQL.PowerShell.GetVESQLDatabas
eCmdlet
line 1374 is this section of the above code:
Code: Select all
#Get the databases in the specified backup and choose Savant
$obj_VBRSQLDatabase = Get-VESQLDatabase -Session "$obj_restorepoint" -Name "Savant"