Untitled

<#
    Author: Daytona Thornton
    Student ID: 010588214
    Script: Restore-SQL.ps1
    Description:
    This script checks for and deletes the existing SQL database 'ClientDB',
    creates a new database and a table named 'Client_A_Contacts',
    imports data from the NewClientData.csv file,
    and exports results to SqlResults.txt.
    It includes exception handling and outputs key messages to the console.
#>

# Define variables
$instance = ".\SQLEXPRESS"
$dbName = "ClientDB"
$tableName = "Client_A_Contacts"
$csvPath = "$PSScriptRoot\NewClientData.csv"
$sqlResults = "$PSScriptRoot\SqlResults.txt"

# Check for existing database and delete it
try {
    Write-Host "`nChecking for existing database '$dbName'..."
    $exists = Invoke-Sqlcmd -ServerInstance $instance -Database master `
        -Query "SELECT name FROM sys.databases WHERE name = '$dbName'" -ErrorAction Stop

    if ($exists) {
        Write-Host "'$dbName' exists. Deleting..."

        # Set to SINGLE_USER mode and drop the database (force delete)
        Invoke-Sqlcmd -ServerInstance $instance -Database master `
            -Query "ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$dbName]"

        Write-Host "'$dbName' deleted."
    } else {
        Write-Host "'$dbName' does not exist."
    }
} catch {
    Write-Host "Error checking/deleting '$dbName': $_"
}

# Create new database
try {
    Write-Host "`nCreating '$dbName'..."
    Invoke-Sqlcmd -ServerInstance $instance -Query "CREATE DATABASE [$dbName]"
    Write-Host "'$dbName' created."
} catch {
    Write-Host "Error creating '$dbName': $_"
}

# Create table
try {
    Write-Host "`nCreating table '$tableName'..."
    $tableQuery = @"
CREATE TABLE [$tableName] (
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Phone NVARCHAR(20),
    Company NVARCHAR(100)
)
"@
    Invoke-Sqlcmd -Database $dbName -ServerInstance $instance -Query $tableQuery
    Write-Host "'$tableName' table created."
} catch {
    Write-Host "Error creating table '$tableName': $_"
}

# Import data from CSV
try {
    Write-Host "`nImporting data from CSV..."

    if (Test-Path $csvPath) {
        Unblock-File -Path $csvPath
        $csv = Import-Csv -Path $csvPath

        foreach ($row in $csv) {
            $query = "INSERT INTO [$tableName] (FirstName, LastName, Email, Phone, Company)
                      VALUES (N'$($row.first_name)', N'$($row.last_name)', N'$($row.email)', N'$($row.phone)', N'$($row.company)')"
            Invoke-Sqlcmd -Database $dbName -ServerInstance $instance -Query $query
        }

        Write-Host "Data imported successfully."
    } else {
        Write-Host "ERROR: CSV file not found at path: $csvPath" -ForegroundColor Red
    }
} catch {
    Write-Host "Error importing data: $_"
}

# Export to file
try {
    Write-Host "`nExporting table contents to $sqlResults..."
    Invoke-Sqlcmd -Database $dbName -ServerInstance $instance `
        -Query "SELECT * FROM [$tableName]" |
        Out-File -FilePath $sqlResults
    Write-Host "SqlResults.txt created successfully."
} catch {
    Write-Host "Error exporting SQL results: $_"
}