Copy Files, Set ACLs, and Alter Database with New File Locations

Copy Set Alter DB Files.ps1

#region Copy Files, Set ACLs, and Alter Database
	foreach ($item in $items) {
        function copyItem ($newLocation, $file, $currentItem) {                 # copyItem Function to copy file
        	$destination = $newLocation + "\" + $file                       # Set destination of file
                Write-Verbose "Moving $file to $destination..." -Verbose
		Copy-Item -Path $currentItem -Destination $destination          # Copy the file to the destination
            return $destination                                                 # Returns the new file location
        }
		$currentItem = $item.CurrentLocation                            # Gets the location of either the .mdf or .ldf file
		$itemExtension = [System.IO.Path]::GetExtension($currentItem)   # Grabs file extension
		$file = [System.IO.Path]::GetFileName($currentItem)             # Grabs full file name
		$name = $item.name
        switch ($itemExtension) {                                               # Instead of using IF Statement
            ".mdf" {                                                            # Is $itemExtension = ".mdf"
                $destination = copyItem -newLocation $mdfNewLocation -file $file -currentItem $currentItem
                $folderLocation = $mdfNewLocation                               # Set $folderLocation
            } 
            ".ldf" {                                                            # Is $itemExtension = ".ldf"
                $destination = copyItem -newLocation $ldfNewLocation -file $file -currentItem $currentItem
                $folderLocation = $ldfNewLocation                               # Set $folderLocation
            }  
            default {                               				# if $itemExtension is neither ".mdf" or ".ldf"
                Write-Verbose "The file extension is not supported..." -Verbose
                break                                                           # Stop if file extension is not ".mdf" or ".ldf"
            }
        }

    	# Set ACLs
		# Change to UTC Path Format												# Change from path based to UTC based format
		$destDrive = Split-Path -qualifier $destination			# Get drive letter
		$utcPath = "\\$env:computername\" + $destDrive.Replace(":","$") # Start creating UTC Path
		$destination = $destination.Replace($destDrive, $utcPath)       # Update $destination to correct path format
                Set-Location $destDrive                                         # Set location just in case it moved to SQLSERVER:
                Write-Verbose "Setting ACL on $destination..." -Verbose
		# Set db to Inherit Permissions
		$acl =  Get-Acl $destination                                    # Get the file's current ACL settings
                Write-Verbose "Setting to Inherit Permissions..." -Verbose
		$acl.SetAccessRuleProtection($false,$false)                     # Enable inheritance and remove non-inherited roles
		# Set Owner of DB to SA Account
		$owner = New-Object System.Security.Principal.NTAccount($sqlSA) # Get the security object for the SQL SA Account
		Write-Verbose "Updating Item Owner to $owner..." -Verbose
                $acl.SetOwner($owner)                                           # Set item owner
		$acl | Set-Acl                                                  # Update the file with ACL updates
		# Update Database
		$query4 = "ALTER DATABASE [" + $dbName + "] MODIFY FILE ( NAME = " + $name + ", FILENAME = '" + $destination + "' );"
                Write-Verbose "Updating Database with new file location..." -Verbose
		Invoke-Sqlcmd -Query $query4                                    # Update database with new file location
	}
    Set-Location $location                                                      # Set location back to original location
#endregion