PowerShell: Microsoft SQL Database Migration

# Set Variables
$orgName='TestOrg'
$databaseName="$orgName`_MSCRM"
$dbData='mscrm'
$dbLog='mscrm_log'
$overwriteFlag=$true
$sourceSqlServer='CRM-SQL01'
$sourceSa='domain\crmSa1'
$sourceSaPassword='somePassword'
$destinationSqlServer='CRM-SQL02'
$destinationSa='domain\crmSa2'
$destinationSaPassword='someOtherPassword'
$logFile='C:\Logs\$orgName_DbShipping.txt'

$serverAdminUsername='domain\adminDude'
$serverAdminPassword='sherverAdminPassword'
$serverAdminCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdminUsername,$(ConvertTo-securestring $serverAdminPassword -AsPlainText -Force)

function addUserToLocalGroup{
    param(
    $computername=$env:computername,
    $localAdminCred,
    [string[]]$accountToAdd,
    $localGroup='Administrators'
    )
    try{
        $session=new-pssession $computername -Credential $localAdminCred -ea Stop
	    }
    catch{
        write-warning "Unable to connect to WinRM of $computername"
        return $false
        }
    invoke-command -session $session -scriptblock{
		param($principleName,$groupName)		
        $members=get-localgroupmember $groupName
        if(!($principleName -in $members.Name)){
            try{
                write-host "Adding $principleName into $groupName";
		        Add-LocalGroupMember -Group $groupName -Member $principleName -ea Stop;
                $currentMembers=get-localgroupmember $groupName|ft|out-string
		        write-host "$principleName has been added to $groupName successfully:`r`n$currentMembers";
		        return $true
                }
            catch{
                write-warning "$error"
                return $false
                }
            }
        else{
            write-host "$principleName is already a member of $groupName."
            return $true}
		} -args $accountToAdd,$localGroup
    remove-pssession $session
}

function shipDatabase{
    param(
        $databaseName,$dbData,$dbLog,$overwrite,$serverAdminCredential
        $sourceSqlServer,$sourceSa,$sourceSaPassword,
        $destinationSqlServer,$destinationSa,$destinationSaPassword
    )

    # Start the timer for this activity
    $stopWatch= [System.Diagnostics.Stopwatch]::StartNew()
    
    # Credentials
    $sourceSaCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $sourceSa,$(ConvertTo-securestring $sourceSaPassword -AsPlainText -Force)
    $destinationSaCred=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $destinationSa,$(ConvertTo-securestring $destinationSaPassword -AsPlainText -Force)

        addUserToLocalGroup $sourceSqlServer $serverAdminCredential $sourceSa
        addUserToLocalGroup $destinationSqlServer $serverAdminCredential $destinationSa

    # 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]

    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
            }
        }
    
    function triggerSqlBackup($sqlServer,$databaseName){
        # Ensure the the Jump Box has SQL PowerShell tools
        $moduleName='SqlServer'    
        if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
            if(!('NuGet' -in (get-packageprovider).Name)){    
                try{
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    }
                catch{
                    Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                    Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                    Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                    }            
                }
            Install-Module -Name $moduleName -Force -Confirm:$false
            }

        # Trigger backup of target database into the default backup directory
        $database=Get-SqlDatabase -ServerInstance $sqlServer | Where { $_.Name -eq $databaseName }
        $backupDuration=(measure-command{Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly -Verbose}).TotalHours    
        return $backupDuration
    }

    $backupDuration=triggerSqlBackup $sourceSqlServer $databaseName

    # Get default backup directory of a SQL server and mount it as the First Available Drive Letter
    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}
                    }
        $validLocal=if($localPath){test-path $localPath -ErrorAction SilentlyContinue}else{$false}
        $validUnc=if($uncPath){test-path $uncPath -ErrorAction SilentlyContinue}else{$false}
        if($validUnc){write-host "$uncPath is reachable, using credentials of $(whoami)"}
        else{write-warning "$computername is unreachable, using credentials of $(whoami)"}
        return $uncPath
    }

    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 @($defaultDataDirectory,$defaultLogDirectory,$defaultBackupDirectory)
            }
        if($defaultValues){return $defaultValues}
        else{return $false}
    }

    # Mount Source UNC path
    $sourceUncPath=convertLocalToUnc $(invokeGetDefaultSqlPaths $sourceSqlServer $sourceSaCred)[2] $sourceSqlServer
    mountDriveAsUser $sourceSA $sourceSaPassword $firstAvailableDriveLetter $sourceUncPath
    $sourceFolder="$firstAvailableDriveLetter`:\"

    # Mount Destination UNC path
    $destinationDefaults=invokeGetDefaultSqlPaths $destinationSqlServer $destinationSaCred
    $destinationUncPath=convertLocalToUnc $destinationDefaults[2] $destinationSqlServer
    mountDriveAsUser $destinationSa $destinationSaPassword $secondAvailableDriveLetter $destinationUncPath
    $destinationFolder="$secondAvailableDriveLetter`:\"

    #if(!(test-path $destinationFolder)){mkdir $destinationFolder}
    #$exportDirectory=$(split-path $backupFile -parent)
    #$fileName=$(split-path $backupFile -leaf)
    #$fileSize=(Get-Item $backupFile).length/1GB
    #$destinationFile="$importDirectory\$fileName"
    #if(get-item $destinationFile -ea SilentlyContinue){rm $destinationFile -Force}

    # Give permissions to current user toward import directory, in case it's not already present
    #[string]$currentAccount=$(whoami)
    #$Acl = Get-Acl $destinationFolder
    #$addPermission = New-Object System.Security.AccessControl.FileSystemAccessRule("$currentAccount", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
    #$Acl.SetAccessRule($addPermission)
    #Set-Acl $destinationFolder $Acl
    #write-host (get-acl $destinationFolder|fl|out-string)

    # Copy backup file to destination
    $sourceBackupFile="$sourceFolder\$databaseName.bak"
    $sanityPassed=(test-path $sourceFolder) -and (test-path $destinationFolder) -and (test-path $sourceBackupFile)
    if ($sanityPassed){
        # rm $destinationFolder\*.* #Purge 
        $fileSize=(get-item $sourceBackupFile).Length/1GB
        $transferTime=(measure-command {robocopy "$sourceFolder" "$destinationFolder" "$databaseName.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"
        }

    # Rename the file to reflect its creation date stamp
    #$pacificTime=[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId( (Get-Date), 'Pacific Standard Time')
    #$dateStamp = $pacificTime.tostring("MM-dd-yyyy-HHmm")
    #Rename-Item -Path "$destinationFolder\$databaseName.bak" -NewName "$databaseName`_$dateStamp_PST.bak"

    $dataFile = ($destinationDefaults[0]+ "\$databaseName.mdf") -replace "\\{2}",'\'
    $logFile = ($destinationDefaults[1]+ "\$databaseName.ldf") -replace "\\{2}",'\'
    $destinationBackupFile="$($destinationDefaults[2])\$databaseName.bak"
    function triggerSqlImport{
    param(
        $sqlServer,
        $databaseName,
        $dataFile,
        $logFile,
        $dbData='mscrm',
        $dbLog='mscrm_log',
        $saCred,
        $backupFile,
        $overWrite
        )
        
        $session=new-pssession $sqlServer -Credential $saCred

        $dbImportResult=invoke-command -Session $session {
            param($databaseName,$backupFile,$dataFile,$logFile,$dbData,$dbLog,$overwrite)
            # Ensure the the Jump Box has SQL PowerShell tools
            $moduleName='sqlps'    
            if(!(Get-Module -ListAvailable -Name $moduleName -ea SilentlyContinue)){
                if(!('NuGet' -in (get-packageprovider).Name)){    
                    try{
                        Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                        }
                    catch{
                        Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                        Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
                        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                        Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force -ErrorAction SilentlyContinue;
                        }            
                    }
                Install-Module -Name $moduleName -Force -Confirm:$false
                }
            import-module $moduleName            
            # Trigger backup of target database into the default backup directory
            #$relocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbData, "$dataFile")
            #$relocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbLog, "$logFile")
            #Restore-SqlDatabase -ServerInstance $sqlServer -Database $databaseName -BackupFile $backupFile -RelocateFile @($relocateData,$relocateLog)
            
            $sqlRestoreOverWrite = @"
            USE [master]
            -- IF DB_ID('$databaseName') IS NULL
            -- BEGIN
            RESTORE DATABASE [$databaseName] FROM  DISK = N'$backupFile' WITH  FILE = 1,
                MOVE N'$dbData' TO N'$dataFile',
                MOVE N'$dbLog' TO N'$logFile',  
                NOUNLOAD,  REPLACE,  STATS = 5
            ALTER DATABASE [$databaseName] 
                SET MULTI_USER
            -- END
"@

            $dbExists=(invoke-sqlcmd "SELECT CASE WHEN DB_ID('$databaseName') IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END").Column1            
            if(!$dbExists){
                write-host "$databaseName is now being added to $env:computername:`r`n$sqlRestoreOverWrite"        
                invoke-sqlcmd $sqlRestoreOverWrite
                }
            elseif($overwrite){
                write-host "$databaseName exists and over-write flag is True. Executing...`r`n$sqlRestoreOverWrite"        
                invoke-sqlcmd $sqlRestoreOverWrite                
                }
            else{
                write-host "$databaseName currently Exists and over-write flag has been set as False"
                }

            # Validation
            CD SQLSERVER:\SQL\$env:computername\DEFAULT
            $currentDb=Get-SqlDatabase -Name $databaseName|select Name,RecoveryModel,CompatibilityLevel,CreateDate,DataSpaceUsage,LastBackupDate,Owner,PrimaryFilePath
            return ($currentDb|out-string).Trim()
            } -Args $databaseName,$backupFile,$dataFile,$logFile,$dbData,$dbLog,$overwrite
        Remove-PSSession $session
        return $dbImportResult
    }

    $importClock= [System.Diagnostics.Stopwatch]::StartNew()
    $importResult=triggerSqlImport $destinationSqlServer $databaseName $dataFile $logFile $dbData $dbLog $destinationSaCred $destinationBackupFile $overwrite
    #write-host $importResult
    $importHours=$importClock.Elapsed.TotalHours;    
    $importClock.Stop();

    # Cleanup Routine
    write-host "Cleaning up backup files at $sourceSqlServer"
    remove-item -path $sourceBackupFile -force
    net use /delete $($firstAvailableDriveLetter+':') 2>&1>null
    net use /delete $($secondAvailableDriveLetter+':') 2>&1>null

    # Total duration
    $hoursElapsed=$stopWatch.Elapsed.TotalHours;
    $stopWatch.Stop();
    $shippingResult="Overall stats`r
-----------------------`r
$importResult`r
Source SQL Server`t: $sourceSqlServer
Destination SQL Server`t: $destinationSqlServer
Database size`t: $([math]::round($fileSize,2))`r
Backup duration (hour)`t: $([math]::round($backupDuration,2))`r
File transfer duration`t: $([math]::round($transferTime,2))`r
DB import duration`t: $([math]::round($importHours,2))`r
Total DB shipping duration`t: $([math]::round($hoursElapsed,2))`r
Aggregate speed`t: $([math]::round($hoursElapsed/$fileSize,2)) GB/Hour`r
-----------------------"
    #write-host $shippingResult
    return $shippingResult
}

shipDatabase $databaseName $dbData $dbLog $overwriteFlag $serverAdminCredential `
                    $sourceSqlServer $sourceSa $sourceSaPassword `
                    $destinationSqlServer $destinationSa $destinationSaPassword `
                    $logFile