PowerShell: Backup Microsoft Dynamics CRM Database

# backupCrmOrgDatabase.ps1
# This script is to be invoked in the context of the Administrator of the Jump Box

# Environment variables
$orgName='TESTORG'
$domainName='hooli.com'
$targetUrl="https://$orgName.$domainName"

# SQL Backup variables
$databaseName="$orgName`_MSCRM"
#$backupLocation="\\$env:computername\P$"
$backupLocation=$null # leave this as null to use the default backup directory
$saUser=$null # leave this as null to use integrated authentication
$saPass=$null # leave this as null to use integrated authentication

# dictionaries
$ipDictionary=@{
  'LAX-DEV'='1.1.1.1','100.100.100.100';
  'LAX-PROD'='2.2.2.2','100.100.100.102';
  'PHX-PROD'='3.3.3.3','100.100.100.103';
  'FLO-PROD'='4.4.4.4','100.100.100.104';
  }
$credentials=@{
'kimconnect\prodAdmin'='password';
'kimconnect\devAdmin'='password';
}

function getEnvironment($url,$dnsServer,$ipDictionary){
    $regexDomain='(http[s]:\/{2}){0,1}([\d\w\.-]+)\/{0,1}'
    $innerUrl=.{[void]($url -match $regexDomain);$matches[2]}
    #$publicIP=[System.Net.Dns]::GetHostAddresses($domain)[0].IPAddressToString
    $publicIP=(Resolve-DnsName -Name $innerUrl -Server $dnsServer -NoHostsFile)[0].IPAddress
    $environment=$ipDictionary.keys | Where-Object {$publicIP -in $ipDictionary["$_"]}
    if ($environment){
        return $environment
        }
    else{write-warning "No environments were matched.";return $false}
}

function invokeSqlBackup($sqlServer,$databaseName,$backupLocation,$adminCredential,$saUser,$saPass){
    $ErrorActionPreference='stop'

    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 backupDatabase($databaseName,$backupLocation,$saUser,$saPass){
        try{
            # Ensure the the Jump Box has SQL PowerShell tools 
            if(!(Get-Module -ListAvailable -Name 'sqlps' -ea SilentlyContinue)){
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                if(!(Get-PackageProvider Nuget -ea SilentlyContinue)){    
                    try{
                        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                        # Preempt this prompt
                        # The provider 'nuget v2.8.5.208' is not installed.
                        # nuget may be manually downloaded from
                        # https://onegetcdn.azureedge.net/providers/Microsoft.PackageManagement.NuGetProvider-2.8.5.208.dll and installed.
                        # Would you like PackageManagement to automatically download and install 'nuget' now?
                        # [Y] Yes  [N] No  [?] Help (default is "Y"):
                        # Find-PackageProvider -Name 'Nuget' -ForceBootstrap -IncludeDependencies # cannot bypass confirmation messages
                        # Install-PackageProvider -Name NuGet -Force -Confirm:$False # cannot bypass prompts
                        # One needs to specify the version to bypass prompts
                        Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
                    }catch{
                        write-warning $error[0].Exception.Message
                        }            
                    }
                try{
                    Install-Module -Name 'SqlServer' -Force -Confirm:$false      
                }catch{
                    write-warning $error[0].Exception.Message
                    return $false
                    }
                }
            
            # Get default locations    
            $saCred=if($saUser -AND $saPass){
                New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminUsername,$encryptedPassword
            }else{
                $false
            }
            [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
            $sqlConnection=if($saCred){
                New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername -credential $saCred
            }else{
                New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
            }
            $defaultBackupDirectory=$sqlConnection.Settings.BackupDirectory
            #$defaultDataDirectory=$sqlConnection.Settings.DefaultFile
            #$defaultLogDirectory=$sqlConnection.Settings.DefaultLog
            
            # A command with name 'Decode-SqlName' is already available on this system. This module 'SqlServer' may override the
            # existing commands. If you still want to install this module 'SqlServer', use -AllowClobber parameter.
            # At line:74 char:13
            # +             Invoke-Command -session $session -scriptBlock {
            # +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            #     + CategoryInfo          : InvalidOperation: (Microsoft.Power....InstallPackage:InstallPackage) [Install-Package],
            #    Exception
            #     + FullyQualifiedErrorId : CommandAlreadyAvailable,Validate-ModuleCommandAlreadyAvailable,Microsoft.PowerShell.Pack
            #    ageManagement.Cmdlets.InstallPackage
            #     + PSComputerName        : sql01

            # Trigger backup of target database into the default backup directory
            $database=if($saCred){
                Get-SqlDatabase -ServerInstance $env:computername -credential $saCred|?{$_.Name -eq $databaseName}
            }else{
                Get-SqlDatabase -ServerInstance $env:computername|?{$_.Name -eq $databaseName}
            }
            $timeStamp=get-date -Format yyyyMMdd-HHmm
            $sqlInstance='MSSQLSERVER' # Assming default instance
            $sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='$sqlInstance'").StartName
            $backupFile=.{if(!$backupLocation){
                    return "$defaultBackupDirectory\$($database.Name)_$timeStamp.bak"
                }else{
                    $backupLocation=if($backupLocation[$backupLocation.Length-1] -eq '\'){$backupLocation[0..$($backupLocation[$backupLocation.Length-2])]}else{$backupLocation}
                    $pathIsUnc=if($backupLocation -match '^\\\\'){$true}else{$false}
                    if($pathIsUnc){
                        return "$backupLocation\$($database.Name)_$timeStamp.bak"
                    }elseif(!(Test-Path $backupLocation)){
                        try{
                            $null=New-Item -ItemType Directory -Force -Path $backupLocation
                            write-host "Granting $env:username and Administrators full access to $backupLocation..."
                            $acl=Get-ACL $backupLocation
                            $originalOwnerAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($acl.Owner,"FullControl","Allow")
                            $userAccess = New-Object System.Security.AccessControl.FileSystemAccessRule($env:username,"FullControl","Allow")
                            $administratorsAccess=New-Object System.Security.AccessControl.FileSystemAccessRule('Administrators',"FullControl","Allow")
                            $acl.AddAccessRule($administratorsAccess)
                            $acl.AddAccessRule($originalOwnerAccess)
                            $acl.AddAccessRule($userAccess)
                            $sqlAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"FullControl","Allow")
                            $sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
                            if(!$sqlHasFullPermissions){
                                $acl.AddAccessRule($sqlAccess)
                            }                           
                            Set-Acl $backupLocation $acl                          
                            return "$backupLocation\$($database.Name)_$timeStamp.bak"
                        }catch{
                            Write-warning $_
                            return $false
                        }
                    }else{
                        return "$backupLocation\$($database.Name)_$timeStamp.bak"
                    }
            }}
            # $tSqlGetMyPermissions="SELECT * FROM fn_my_permissions(NULL, '$env:Computername')"
            # Invoke-Sqlcmd -Query $tSqlGetMyPermissions -ServerInstance "$env:computername\$sqlInstance"
            if($backupFile){
                if($saCred){
                    Backup-SqlDatabase -DatabaseObject $database -credential $saCred -CompressionOption On -CopyOnly -BackupFile $backupFile
                }else{
                    Backup-SqlDatabase -DatabaseObject $database -CompressionOption On -CopyOnly -BackupFile $backupFile
                }
                # Error caused by lack of permissions, which means that the provided cred needs to be added to SQL Server with a sysadmin role
                # Backup-SqlDatabase : System.Data.SqlClient.SqlError: BACKUP DATABASE permission denied in database
                $backupFileSize=try{(get-item $backupfile).Length}catch{}
                return @($backupFile,$backupFileSize)
            }
        }catch{
            Write-Warning $error[0].Exception.Message
            return $false
            }
    }

    function addUserToGroup{
        param(
            $computername=$env:computername,
            [string[]]$accountToAdd=$env:username,
            [string]$groupName='Administrators',
            [pscredential]$localAdminCred
            )

        $session=if($localAdminCred){
                new-pssession $computername -Credential $localAdminCred -ea Stop
            }else{
                new-pssession $computername -ea Stop
            }

        if($session.State -eq 'Opened'){
        invoke-command -session $session -scriptblock{
            param($accountToAdd,$groupName)		
            $members=get-localgroupmember $groupName
            if(!($accountToAdd -in $members.Name)){
                try{
                    write-host "Adding $accountToAdd into $groupName";
                    Add-LocalGroupMember -Group $groupName -Member $accountToAdd -ea Stop;
                    $currentMembers=get-localgroupmember $groupName|ft|out-string
                    write-host "$accountToAdd has been added to $groupName successfully:`r`n$currentMembers";
                    return $true
                    }
                catch{
                    write-warning "$error"
                    return $false
                    }
                }
            else{
                write-host "$accountToAdd is already a member of $groupName."
                return $true}
            } -args $accountToAdd,$groupName
        remove-pssession $session
        }else{
            write-warning "WinRM connection was not successful on $computerName"
            return $false
        }
    }

    try{
        $pathIsUnc=if($backupLocation -match '^\\\\'){$true}else{$false}
        $pathIsAdminShare=if($pathIsUnc){$backupLocation -match '\w{1}\$'}else{$false}
        $sqlRunas=(Get-WMIObject Win32_Service -ComputerName $sqlServer -Filter "Name='MSSQLSERVER'").StartName
        if($pathIsAdminShare){
            write-host "Granting $sqlRunas permissions on $backupLocation Admin Share..."
            $fileServerName=[regex]::Match($backupLocation,'^\\\\([\w-]+)').captures.groups[1].value
            addUserToGroup $fileServerName $sqlRunas 'Administrators'            
        }elseif($pathIsUnc){
                if(!(test-path $backupLocation)){$null=New-Item -ItemType Directory -Force -Path $backupLocation}
                $acl=Get-ACL $backupLocation
                $env:username,$acl.Owner,$sqlRunas|%{
                    $accessAlreadyExists=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
                    if(!$accessAlreadyExists){                    
                        $grantAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($_,"FullControl","Allow")
                        $acl.AddAccessRule($grantAccess)
                        write-host "Granted $_ full access to $backupLocation..."
                    }
                }       
                Set-Acl $backupLocation $acl
            }
    }catch{
        write-warning $_
    }
    
    $session=if($adminCredential){
            New-PSSession -ComputerName $sqlServer -Credential $adminCredential
        }else{
            New-PSSession -ComputerName $sqlServer
        }
    if($session.state -eq 'Opened'){
        $result=Invoke-Command -session $session -scriptBlock {
            param($backupDatabase,$databaseName,$backupLocation,$saUser,$saPass)
            [scriptblock]::create($backupDatabase).Invoke($databaseName,$backupLocation,$saUser,$saPass)
        } -Args ${function:backupDatabase},$databaseName,$backupLocation,$saUser,$saPass
        Remove-PSSession $session
        return $result
    }else{
        write-warning "WinRM connections are not available at target $sqlServer"
        return $false
    }    
}

$defaultInterface=get-wmiobject win32_networkadapterconfiguration -filter "ipenabled='true'"|?{$_.DefaultIpGateway -ne $null}
$defaultDnsServer=$defaultInterface.DNSServerSearchOrder|Select-Object -first 1
$environment=getEnvironment $targetUrl $defaultDnsServer $ipDictionary
$sqlServer="$environment-sql01"
$adminUsername="corp\crm$environment"
$adminPassword=$credentials[$adminUsername]
$encryptedPassword=ConvertTo-SecureString $adminPassword -AsPlainText -Force
$adminCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminUsername,$encryptedPassword
if($environment){
    $backupInfo=invokeSqlBackup $sqlServer $databaseName $backupLocation $adminCredential $saUser $saPass
    $fileSize=if($backupInfo[1]){$backupInfo[1]}else{get-item $backupInfo[1]}
    write-host "$($backupInfo[0])`: $($fileSize.Length/1GB) GB"
}

Leave a Reply

Your email address will not be published. Required fields are marked *