# backupCrmOrgDatabase.ps1
# This script is to be invoked in the context of the Administrator of the Jump Box
# Assumptions:
# - SQL Server is configured with Integrated Authentication where Local Admininistrator or Domain Account has sysadmin roles
$orgNames=@(
'TESTORG',
'TESTORG1',
'TESTORG2'
)
$domainName='kimconnect.com'
$sqlServerSuffix='-sql01'
$usernamePrefix='corp\crmAdmin'
$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=@{
'dev'='devPublicIP','devPrivateIP';
'cluster1'='cluster1PublicIP','cluster1PrivateIP';
'cluster2'='cluster2PublicIP','cluster2PrivateIP'
}
$credentials=@{
'corp\crmAdmin'='PASSWORDHERE';
}
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
}
}
# Generate dynamic variables
$defaultInterface=get-wmiobject win32_networkadapterconfiguration -filter "ipenabled='true'"|?{$_.DefaultIpGateway -ne $null}
$defaultDnsServer=$defaultInterface.DNSServerSearchOrder|Select-Object -first 1
$orgsInfo=@()
foreach($orgName in $orgNames){
$targetUrl="https://$orgName.$domainName"
$environment=getEnvironment $targetUrl $defaultDnsServer $ipDictionary
$sqlServer=$environment+$sqlServerSuffix
$adminUsername=$usernamePrefix+$environment
$adminPassword=$credentials[$adminUsername]
$databaseName="$orgName`_MSCRM"
$orgsInfo+=[pscustomobject]@{
orgname=$orgname
environment=$environment
adminUsername=$adminUsername
adminPassword=$adminPassword
sqlServer=$sqlServer
databaseName=$databaseName
}
}
foreach($org in $orgsInfo){
if($org.environment -and $org.adminUsername -and $org.adminPassword){
$backupLocation=if($backupLocation){$backupLocation}else{$null}
$encryptedPassword=ConvertTo-SecureString $org.adminPassword -AsPlainText -Force
$adminCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $org.adminUsername,$encryptedPassword
$backupInfo=invokeSqlBackup $org.sqlServer $org.databaseName $backupLocation $adminCredential $null $null
$fileSize=if($backupInfo[1]){$backupInfo[1]}else{get-item $backupInfo[1]}
write-host "$($backupInfo[0])`: $($fileSize.Length/1GB) GB"
$org|Add-Member -NotePropertyName filePath -NotePropertyValue $backupInfo[0]
$org|Add-Member -NotePropertyName fileSize -NotePropertyValue $fileSize
}else{
$org|Add-Member -NotePropertyName filePath -NotePropertyValue $null
$org|Add-Member -NotePropertyName fileSize -NotePropertyValue $null
}
}
Write-Output $orgsInfo
Categories: