# User defined variables
$sourceSqlServer='DEV-SQL01'
$sourceDatabaseName='TEST_MSCRM'
$sourceSaCredential=$(get-credential)
$destinationSqlServer='DEV-SQL01'
$destinationDatabaseName='Test_MSCRM'
$destinationSaCredential=$(get-credential)
function copyDatabase{
param(
$sourceSqlServer,
$sourceDatabaseName,
$sourceSaCredential,
$destinationSqlServer,
$destinationDatabaseName,
$destinationSaCredential
)
function convertLocalToUnc($localPath,$computername){
$uncPath=.{$x=$localPath -replace "^([a-zA-Z])\:","\\$computername\`$1`$";
if($x -match '\\$'){return $x.Substring(0,$x.length-1)}else{return $x}
}
return $uncPath
}
function mountDriveAsUser($username,$password,$driveLetter,$uncPath){
if(get-psdrive $driveLetter -ea SilentlyContinue){
#Remove-PSDrive $firstAvailableDriveLetter -ea SilentlyContinue #This does not affect drives being mounted by 'net use' command
net use /delete ($driveLetter+':') 2>&1>null
}
try{
# This command cannot persist when out of scope of function; hence, net use is required
# New-PSDrive –Name $mountLetter –PSProvider FileSystem –Root $uncPath –Persist -Credential $mountAsCred|out-null
net use "$driveLetter`:" "$uncPath" /user:$username $password /persistent:Yes 2>&1>null
if(test-path "$driveLetter`:\"){
write-host "$driveLetter`: has successfully mounted.";
#return $true;
}
else{
write-host "Unable to mount drive $driveLetter";
#return $false
}
}
catch{
write-warning "$error"
#return $false
}
}
$triggerSqlBackup={
param ($databaseName)
try{
import-module sqlps
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$database=Get-SqlDatabase -ServerInstance $env:computername|?{$_.Name -eq $databaseName}
Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly
return $true
}catch{
write-warning $_
return $false
}
}
$restoreDbChangeName={
param(
$dbBackupFile,
$newDatabaseName,
$dbData='mscrm',
$dbLog='mscrm_log'
)
import-module sqlps
function confirmation($content,$testValue="I confirm",$maxAttempts=3){
$confirmed=$false;
$attempts=0;
$content|write-host
write-host "Please review this content for accuracy.`r`n"
while ($attempts -le $maxAttempts){
if($attempts++ -ge $maxAttempts){
write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
break;
}
$userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
if ($userInput.ToLower() -eq $testValue.ToLower()){
$confirmed=$true;
write-host "Confirmed!`r`n";
break;
}elseif($userInput -like 'cancel'){
write-host 'Cancel command received.'
$confirmed=$false
break
}else{
Clear-Host;
write-host $($content|out-string).Trim()
write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
}
}
return $confirmed;
}
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
$defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
$defaultDataDirectory=$sqlConnection.Settings.DefaultFile
$defaultLogDirectory=$sqlConnection.Settings.DefaultLog
$sqlDefaults=@{
'dataDirectory'=$defaultDataDirectory
'logDirectory'=$defaultLogDirectory
'backupDirectory'=$defaultBackupDirectory
}
#$originalDb=invoke-sqlcmd -query "restore filelistonly from disk = '$dbBackupFile'"
#$originalDbDataPath=$originalDb[0].PhysicalName
#$originalDbLogPath=$originalDb[1].PhysicalName
$sqlRestoreChangeDbName="
USE [master]
GO
ALTER DATABASE [$newDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE [$newDatabaseName] FROM DISK = N'$dbBackupFile' WITH FILE = 1,
MOVE N'$dbData' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.mdf',
MOVE N'$dbLog' TO '$($sqlDefaults.dataDirectory)$newDatabaseName.ldf',
NOUNLOAD, REPLACE, STATS = 5;
GO
"
$confirmed=confirmation -content $sqlRestoreChangeDbName
if($confirmed){
try{
invoke-sqlcmd -Query $sqlRestoreChangeDbName -QueryTimeout $([int]::MaxValue) -ConnectionTimeout 0 -Verbose
$removeBackupFile=confirmation -content "Delete backup file: $dbBackupFile?"
if($removeBackupFile){
$null=remove-item -path $dbBackupFile -force -ea SilentlyContinue
}else{
write-host "Backup file NOT removed: $dbBackupFile"
}
return $true
}catch{
write-warning $_
return $false
}
}else{
write-host 'No confirmations received. No changes have been made.'
return $false
}
}
function invokeGetDefaultSqlPaths($sqlServer,$saCredential){
$defaultValues=invoke-command -ComputerName $sqlServer -Credential $saCredential -ScriptBlock{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$sqlConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
$defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
$defaultDataDirectory=$sqlConnection.Settings.DefaultFile
$defaultLogDirectory=$sqlConnection.Settings.DefaultLog
return @{
'dataDirectory'=$defaultDataDirectory
'logDirectory'=$defaultLogDirectory
'backupDirectory'=$defaultBackupDirectory
}
}
if($defaultValues){return $defaultValues}
else{return $false}
}
$successfulBackup=invoke-command -computername $sourceSqlServer `
-credential $sourceSaCredential `
-scriptBlock $triggerSqlBackup `
-Args $sourceDatabaseName
if($successfulBackup){
if ($sourceSqlServer -ne $destinationSqlServer){
# Extract username and passwords to be used as mounts
$sourceUsername=$sourceSaCredential.Username
$sourcePassword=$sourceSaCredential.GetNetworkCredential().password
$destinationUsername=$destinationSaCredential.Username
$destinationPassword=$destinationSaCredential.GetNetworkCredential().password
# Scan for next available drive letter, excluding D "CD Rom" and H "Home"
$unavailableDriveLetters=(Get-Volume).DriveLetter|sort
$availableDriveLetters=.{(65..90|%{[char]$_})|?{$_ -notin $unavailableDriveLetters}}
[char]$firstAvailableDriveLetter=$availableDriveLetters[0]
[char]$secondAvailableDriveLetter=$availableDriveLetters[1]
# Mount Source UNC path
$sourceSqlPaths=invokeGetDefaultSqlPaths $sourceSqlServer $sourceSaCredential
$sourceUncPath=convertLocalToUnc $sourceSqlPaths.BackupDirectory $sourceSqlServer
mountDriveAsUser $sourceUsername $sourcePassword $firstAvailableDriveLetter $sourceUncPath
$sourceFolder="$firstAvailableDriveLetter`:\"
# Mount Destination UNC path
$destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential
$destinationUncPath=convertLocalToUnc $destinationSqlPaths.BackupDirectory $destinationSqlServer
mountDriveAsUser $destinationUsername $destinationPassword $secondAvailableDriveLetter $destinationUncPath
$destinationFolder="$secondAvailableDriveLetter`:\"
$sourceBackupFile="$sourceFolder$sourceDatabaseName.bak"
$sanityPassed=(test-path $sourceFolder) -and (test-path $destinationFolder) -and (test-path $sourceBackupFile)
if ($sanityPassed){
write-host "Now copying $sourceBackupFile to $destinationFolder. Please wait..."
$fileSize=(get-item $sourceBackupFile).Length/1GB
$transferTime=(measure-command {robocopy "$sourceFolder" "$destinationFolder" "$sourceDatabaseName.bak"}).TotalHours
$transferSpeed=[math]::round($fileSize/$transferTime,2)
write-host "$([math]::round($fileSize,2)) GB was copied in $([math]::round($transferTime,2)) hours => Speed: $transferSpeed GB/Hour"
}else{
write-warning "Unable to reach one of these paths:`r`n$sourceFolder`r`n$destinationFolder`r`n$sourceBackupFile"
}
# Cleanup Routine
write-host "Deleting backup file at $sourceBackupFile..."
$null=remove-item -path $sourceBackupFile -force -ea SilentlyContinue
write-host "Removing drive mounts..."
net use /delete $($firstAvailableDriveLetter+':') 2>&1>null
net use /delete $($secondAvailableDriveLetter+':') 2>&1>null
}else{
$destinationSqlPaths=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCredential
}
$destinationBackupFile="$($destinationSqlPaths.BackupDirectory)\$sourceDatabaseName.bak"
$result=invoke-command -computername $destinationSqlServer `
-credential $destinationSaCredential `
-scriptBlock $restoreDbChangeName `
-Args $destinationBackupFile,$destinationDatabaseName
return $result
}else{
write-warning "SQL Backup has failed at the source server $sourceSqlServer"
return $false
}
}
copyDatabase $sourceSqlServer `
$sourceDatabaseName `
$sourceSaCredential `
$destinationSqlServer `
$destinationDatabaseName `
$destinationSaCredential
September 26, 2020September 26, 2020
0 Comments