Created
February 8, 2017 12:10
-
-
Save Stuart-Moore/19e730d58e4c91d8ef753663ac8488fb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function Invoke-DbaDBRestoreSeeding | |
{ | |
<# | |
.SYNOPSIS | |
Syncs 2 databases ready for database mirroring or Availability Group setup | |
.DESCRIPTION | |
Scans the database backup history on the Source SQL Instance, and restores them onto the source Instance | |
If more transanction backups occur before the restores complete, these will then be applied afterwards. | |
The function may also be used to resume an already partially restored databas as long as it's based on the same full database backup chain | |
Returns $true on success, and $false otherwise | |
.PARAMETER SourceSqlInstance | |
The source SQL instance for the database to be seeded from | |
.PARAMETER SourceSqlCredential | |
A PSCredential object to authenticate to the Source SQL instance. If not provided Trusted auth will be used | |
.PARAMETER DestinationSqlInstance | |
The destination SQL instance for the database to be seeded to | |
.PARAMETER DestinationSqlCredential | |
A PSCredential object to authenticate to the Destsination SQL instance. If not provided Trusted auth will be used | |
.PARAMETER Database | |
The name of the database to be seeded | |
.PARAMETER Replace | |
A switch to indicate that if the database already exists on the Destination instance it should be overwritten | |
.PARAMETER Resume | |
A switch to indicated that the seeding should carry on from an earlier attempt if possible | |
#> | |
[CmdletBinding()] | |
Param ( | |
[parameter(Mandatory = $true)] | |
[object]$SourceSqlInstance, | |
[parameter(Mandatory = $true)] | |
[object]$DestinationSqlInstance, | |
[System.Management.Automation.PSCredential]$SourceSqlCredential, | |
[System.Management.Automation.PSCredential]$DestinationSqlCredential, | |
[String]$Database, | |
[Switch]$Replace, | |
[Switch]$Resume | |
) | |
$FunctionName = "Invoke-DbaDBRestoreSeeding" | |
Write-Verbose "$FunctionName - Starting" | |
Try | |
{ | |
$SourceServer = Connect-SqlServer -SqlServer $SourceSqlInstance -SqlCredential $SourceSqlCredential | |
} | |
Catch | |
{ | |
Write-Error "$FunctionName - Can't connect to Source Instance" | |
return $false | |
} | |
Try | |
{ | |
$DestServer = Connect-SqlServer -SqlServer $DestinationSqlInstance -SqlCredential $DestinationSqlCredential | |
} | |
Catch | |
{ | |
Write-Error "$FunctionName - Can't connect to Destination Instance" | |
return $false | |
} | |
if (($DestServer.databases[$Database].length -ne 0) -and ($Replace -eq $false -or $resume -eq $false )) | |
{ | |
Write-Error "$FunctionName - Database exists, and we've not been told to replace it." | |
return $false | |
break | |
} | |
$LastFullBackup = (Get-DbaBackupHistory -SqlServer $SourceSqlInstance -Databases $Database -LastFull -credential $SourceSqlCredential).start | |
$BackupHistory = get-dbabackuphistory -SqlServer $SourceSqlInstance -databases $Database -since $LastFullBackup -credential $SourceSqlCredential | |
$RestoreFiles = $BackupHistory | select @{Name="Fullname";Expression={$_.path}} | Get-FilteredRestoreFile -SqlServer $SourceSqlInstance -Credential $SourceSqlCredential | |
if ($resume -eq $True -and $DestServer.DataBases[$Database].length -ne 0 -and $DestServer.DataBases[$Database].length -eq 'Restoring') | |
{ | |
#Get dest LSN and filter restore files appropriately. | |
$DestinationLastLSN = $DestinationSqlInstance.Databases[$Database].EnumBackupSets() | sort-object -Descending -Property LastLSN | select-object -property LastLSN -First 1 | |
$RestoreFiles = $RestoreFiles | Where-Object {$_.LastLSN -gt $DestinationLastLSN} | |
} | |
elseif ($resume -eq $true -and ($DestServer.DataBases[$Database].length -eq 0 -or $DestServer.DataBases[$Database].length -ne 'Restoring')) | |
{ | |
Write-Error "$FunctionName - Cannot resume if Database does not exist or is not in restoring mode" | |
return $false | |
} | |
try | |
{ | |
$RestoreFiles | Restore-DbFromFilteredArray -SqlServer $DestinationSqlInstance -SqlCredential $DestinationSqlCredential -Databases $Database -NoRecovery -Replace:$Replace | |
} | |
catch | |
{ | |
Write-Error "$FunctionName - Failure in restore proccess" | |
return $false | |
} | |
$SourceLastLSN = $SourceSqlInstance.Databases[$Database].EnumBackupSets() | sort-object -Descending -Property LastLSN | select-object -property LastLSN -First 1 | |
$DestinationLastLSN = $DestinationSqlInstance.Databases[$Database].EnumBackupSets() | sort-object -Descending -Property LastLSN | select-object -property LastLSN -First 1 | |
if ($SourceLastLSN -gt $DestinationLastLSN) | |
{ | |
$recurse = Invoke-DbaDBRestoreSeeding -Source $SourceSqlInstance -Destination $DestinationSqlInstance -SourceSqlCredential $SourceSqlCredential -DestinationSqlCredential $DestinationSqlCredential -Database $Database -Resume:True | |
if ($recurse -eq $true) | |
{ | |
return $true | |
} | |
} | |
elseif ($SourceLstLSN -lt $DestinationLastLSN) | |
{ | |
Write-Error "$FunctionName - Ahead of Source database, aborting" | |
return $false | |
} | |
else | |
{ | |
#We're done, notify, or set up mirroing/AG | |
return $true | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment