Function to invoke SQL Backups on a Remote SQL Server:
# invokeSqlBackup.ps1
# Version 0.0.1
# This iteration requires that the backup directory be a local path, rather than UNC Path
$sqlServer='sql01'
$databaseName='TEST_MSCRM'
$backupLocation='X:\Backup\Test'
$saCred=$null # leave this null to use integrated authentication
function invokeSqlBackup($sqlServer,$databaseName,$backupLocation,$saCred){
$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){
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
[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
# 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=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])] -join ''|out-string).trim()}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){
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
return $backupFile
}
}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($saCred){
New-PSSession -ComputerName $sqlServer -Credential $saCred
}else{
New-PSSession -ComputerName $sqlServer
}
if($session.state -eq 'Opened'){
$result=Invoke-Command -session $session -scriptBlock {
param($backupDatabase,$databaseName,$backupLocation)
[scriptblock]::create($backupDatabase).Invoke($databaseName,$backupLocation)
} -Args ${function:backupDatabase},$databaseName,$backupLocation
Remove-PSSession $session
return $result
}else{
write-warning "WinRM connections are not available at target $sqlServer"
return $false
}
}
invokeSqlBackup $sqlServer $databaseName $backupLocation $saCred
Older Iteration:
# backupSqlToUnc.ps1
# Function is to be invoked at the source SQL server
$backupPath='\\UNCSERVER\backup'
$databaseName='SOMEDB'
function backupDatabase{
param(
$databaseName,
$backupPath
)
# Ensure that SQL Server runas account has full access to the backup Path
try{
$sqlInstance='MSSQLSERVER'
$sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='MSSQLSERVER'").StartName
$acl=Get-ACL $backupPath
$sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
if(!$sqlHasFullPermissions){
$allowFullAccesss=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"Full","Allow")
$acl.AddAccessRule($allowFullAccesss)
write-host "$sqlRunas has been granted full access to $backupPath"
}else{
write-host "$sqlRunas already has full access to $backupPath"
}
}catch{
write-warning $_
}
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 -BackupFile "$backupPath\$databaseName`.bak" -CompressionOption On -CopyOnly
return $true
}catch{
Write-Error $_
return $false
}
}
backupDatabase $databaseName $backupPath
# Alternative method - not used
#New-PSDrive -Name $firstAvailableDriveLetter -PSProvider FileSystem -Root $backupPath -Credential $sqlRunas -Persist
#$password='whatpassword?'
#net use "$driveLetter`:" "$backupPath" /user:$sqlRunas $password /persistent:Yes 2>&1>null