1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<#
.SYNOPSIS
    This PowerShell script exports data from specified SQL Server tables to CSV files.
.DESCRIPTION
    The script connects to a SQL Server database and exports data from specified tables to CSV files.
    The script can export data from specific tables or all tables in the database based on the provided parameters.
#>

# Define script parameters with default values
param (
    [string]$sqlUsername = "SuperAdmin",                        
    [string]$sqlPassword = "**********",                        
    [string]$serverName = "DESKTOP-*******",                    
    [string]$databaseName = "PBI_Inventory",                   
    [string]$outputPath = "C:\Users\ted\Downloads",        
    [String[]]$tables = @("Users", "Refreshes"),               
    [bool]$DownloadAllTable = $false                             
)

# Function to establish a SQL Server connection
function Get-SqlConnection {
    param (
        [string]$server,        
        [string]$database,      
        [string]$username,      
        [string]$password       
    )

    $connStr = "Server=$server;Database=$database;User Id=$username;Password=$password"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $connection.Open()
    
    return $connection
}

# Function to export data from a table to a CSV file
function Export-TableData {
    param (
        [System.Data.SqlClient.SqlConnection]$connection,  
        [string]$table,                                    
        [string]$outputPath                                
    )

    $outputFile = Join-Path $outputPath "$($table).csv"
    $query = "SELECT * FROM $table"
    
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataTable = New-Object System.Data.DataTable
    $dataAdapter.Fill($dataTable)
    
    $dataTable | Export-Csv -Path $outputFile -NoTypeInformation
    Write-Host "Exported table $table to $outputFile."
}

$TodaysDate = Get-Date -Format "yyyyMMdd"

try {
    $connection = Get-SqlConnection -server $serverName -database $databaseName -username $sqlUsername -password $sqlPassword

    if ($DownloadAllTable) {
        $queryTable = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"
        $tables = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Username $sqlUsername -Password $sqlPassword -Query $queryTable -TrustServerCertificate | Select-Object -ExpandProperty TABLE_NAME
    }

    $folderPath = Join-Path $outputPath "$($databaseName)_$TodaysDate"

    if (-not (Test-Path $folderPath)) {
        New-Item -ItemType Directory -Path $folderPath
    }

    foreach ($table in $tables) {
        Export-TableData -connection $connection -table $table -outputPath $folderPath
    }
}
catch {
    Write-Host "Error: $_"
}
finally {
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
}