Untitled

<#
.SYNOPSIS
 This script restores active directory OU's and reconfigures SQL server.
.DESCRIPTION
A. Create a PowerShell script named “restore.ps1” within the “Requirements2” folder.
 For the first line, create a comment and include your first and last name along with your student ID.
Note: The remainder of this task shall be completed within the same script file, “restore.ps1.”
B. Write a single script within the “restore.ps1” file that performs all of the following functions without
user interaction:
1. Create an Active Directory organizational unit (OU) named “finance.”
2. Import the financePersonnel.csv file (found in the “Requirements2” directory) into your Active
Directory domain
 and directly into the finance OU. Be sure to include the following properties:
 • First Name
 • Last Name
 • Display Name (First Name + Last Name, including a space between)
 • Postal Code
 • Office Phone
 • Mobile Phone
3. Create a new database on the UCERTIFY3 SQL server instance called “ClientDB.”
4. Create a new table and name it “Client_A_Contacts.” Add this table to your new database.
5. Insert the data from the attached “NewClientData.csv” file (found in the “Requirements2” folder)
into the table created in part B4.
C. Apply exception handling using try-catch for System.OutOfMemoryException.
D. Run the script within the uCertify environment. After the script executes successfully,
 run the following cmdlets individually from within your Requirements2 directory:
1. Get-ADUser -Filter * -SearchBase “ou=finance,dc=ucertify,dc=com” -Properties
DisplayName,PostalCode,OfficePhone,MobilePhone > .\AdResults.txt
2. Invoke-Sqlcmd -Database ClientDB –ServerInstance .\UCERTIFY3 -Query ‘SELECT * FROM
dbo.Client_A_Contacts’ > .\SqlResults.txt
Note: Ensure you have all of the following files intact within the “Requirements2” folder, including the
original files:
 • “restore.ps1”
 • “AdResults.txt”
 • “SqlResults.txt”
.NOTES
 Version: 1.0
 Author: Michael Robinson
 Creation Date: 15 August, 2020
 Purpose/Change: Initial script development
#>
# Requires -version 2
## PARAMETERS
Param (
 [string]$OUName = "finance",
 [string]$ADUsersCSVPath = "$PSScriptRoot\Requirements2\financePersonnel.csv",
 [string]$SQLDataCSVPath = "$PSScriptRoot\Requirements2\NewClientData.csv",
 [string]$OUPath = "DC=ucertify,DC=com",
 [string]$Database = "ClientDB",
 [string]$Servername = ".\UCERTIFY1"
)
## VARIABLES
## FUNCTIONS
Function Add-ADOU {
 Param (
 [Parameter(Mandatory = $true)]
 [string]$OUName,
 [Parameter(Mandatory = $false)]
 [string]$OUPath = "DC=ucertify,DC=com"
 )
 Write-Host -ForegroundColor Cyan "Configuring AD"
 Write-Host -ForegroundColor Yellow "Creating OU " $OUName
 ## Create New AD Organizational Unit
 New-ADOrganizationalUnit -Name $OUName -Path $OUPath
 Write-Host -ForegroundColor Green "Done"
}
Function Import-ADUsers {
 Param (
 [Parameter(Mandatory)]
 [string]$BackupCsvPath,
 [Parameter(Mandatory)]
 [string]$OUPath
 )
 Write-Host -ForegroundColor Yellow "Importing Users"
 ## Import CSV File
 $BackupADUsers = Import-CSV $BackupCSVPath

 # Fix naming from .csv file
 $ADUsers = $BackupADUsers | Select-Object `
 @{Name = 'SamAccountName' ; Expression = {$_.first_name + $_.last_name}},
 @{Name = 'Name' ; Expression = {$_.first_name + " " + $_.last_name}},
 @{Name = 'DisplayName' ; Expression = {$_.first_name + " " + $_.last_name}},
 @{Name = 'GivenName' ; Expression = {$_.first_name}},
 @{Name = 'Surname' ; Expression = {$_.last_name}},
 city,
 @{Name = 'PostalCode' ; Expression = {$_.zip}},
 @{Name = 'OfficePhone' ; Expression = {$_.officePhone}},
 @{Name = 'MobilePhone' ; Expression = {$_.mobilePhone}}
 ## Create Users from given data
 $ADUsers | New-ADUser -Path $OUPath

 Write-Host -ForegroundColor Green "Done"
}
Import-Module -Name sqlps -DisableNameChecking
Function Add-SQLDB {
 Write-Host -ForegroundColor Cyan "Configuring SQL"
 Write-Host -ForegroundColor Yellow "Creating Database " $Database
 $Servername = ".\UCERTIFY3"
 ## Create Database
 $svr = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Servername
 $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList
$Servername, $Database
 $db.Create()
 Write-Host -ForegroundColor Green $db.Name "Created" $db.CreateDate
 ## Add Table
 # Define Table
 Write-Host -ForegroundColor Yellow "Creating Table " $Table
 $CreateTable = @"
Use ClientDB
CREATE TABLE Client_A_Contacts
(
first_name varchar(100) NOT NULL,
last_name varchar(100) NOT NULL,
samAccount varchar(100) NOT NULL,
city varchar(100) NOT NULL,
county varchar(100) NOT NULL,
zip int NOT NULL,
officePhone varchar(20) NOT NULL,
mobilePhone varchar(20) NOT NULL
)
"@
 # Create Table
 Invoke-Sqlcmd -ServerInstance $Servername -Database $Database -Query $CreateTable
 Write-Host -ForegroundColor Green "Done"
}
Function Import-SQLData {
 Param (
 [Parameter(Mandatory)]
 [string]$CSVPath,
 [Parameter(Mandatory)]
 [string]$Database,
 [Parameter(Mandatory)]
 [string]$Servername
 )
 Write-Host -ForegroundColor Yellow "Importing SQL Data"
# Import SQL Data

 $SQLData = Import-Csv $CSVPath
 # Prepare Query
 $TableData = @"
INSERT INTO dbo.Client_A_Contacts
VALUES
"@
 # Insert Data
 $n = 0
 ForEach ( $user in $SQLData ) {
 if($n -ne 0){
 $TableData += ",`n"
 }
 $n = 1
 $TableData += "('" + $user.first_name
 $TableData += "', '" + $user.last_name
 $TableData += "', '" + $user.samAccount
 $TableData += "', '" + $user.city
 $TableData += "', '" + $user.county
 $TableData += "', " + $user.zip
 $TableData += ", '" + $user.officePhone
 $TableData += "', '" + $user.mobilePhone
 $TableData += "')"
 }
 # Invoke SQL Command to Import Data into Table
 Invoke-Sqlcmd -ServerInstance $Servername -Database $Database -Query $TableData
 Write-Host -ForegroundColor Green "Done"
}
## EXECUTION
$Servername = ".\UCERTIFY3"
Add-ADOU -OUName $OUName
$OUPath = "OU=" + $OUName + "," + $OUPath
Import-ADUsers -BackupCsvPath $ADUsersCSVPath -OUPath $OUPath
Add-SQLDB -Database $Database -SqlServer $Servername
Import-SQLData -CSVPath $SQLDataCSVPath -Database $Database -Servername $Servername