Creates a dacpac file from a SQL Server database, with FULL SCHEMA and TABLE DATA, but optionally EXCLUDES specific table data

1 - BackupDatabase.ps1

# Creates a dacpac file from a SQL Server database, with FULL SCHEMA and TABLE DATA, but optionally EXCLUDES specific table data
# Designed for use in GitHub Actions
# Example Usage: 
# .\BackupDatabase.ps1 -DatabaseName "rtlc-prod" -ConnectionString "data source=.\SQLEXPRESS;initial catalog=rtlc-prod;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;TrustServerCertificate=True" -ExludeTableNames "Log4net,LogEvent,__MigrationHistory" -DacpacOutputPath "C:\Users\joshh\AppData\Local\Temp"

# Target Database & Parameters
param (
    [Parameter(Mandatory = $true)]
    [string]$DatabaseName,
    [Parameter(Mandatory = $true)]
    [string]$ConnectionString,
    [Parameter(Mandatory = $false)]
    [string]$ExludeTableNames = "None", # Comma separated list of table names to exclude from data export
    [Parameter(Mandatory = $false)]
    [string]$DacpacOutputPath = (Split-Path $PSCommandPath) # Default to current dir
)

# Target File Name and Path
$isoDateTime = Get-Date -Format "yyyy-MM-dd.HH-mm-ss"
$outputFileName = "$DatabaseName`_$isoDateTime.dacpac"
$fullFilePath = Join-Path $DacpacOutputPath $outputFileName
Write-Host "Exporting $DatabaseName to $fullFilePath"

# Table list as Properties for SqlPackage Query
$exludeTablesListForQuery = ($ExludeTableNames -split ',' | ForEach-Object { "'$_'" }) -join ',' # Wrap each table in single quotes, convert to comma separated string for query
Write-Host "Excluding tables: $exludeTablesListForQuery"

$tableListAsPropertiesQuery = "
SELECT STRING_AGG(CONCAT('/p:TableData=', TABLE_SCHEMA, '.', TABLE_NAME), ',') as TableNamesList
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME NOT IN ($exludeTablesListForQuery)"
# Write-Host $tableListAsPropertiesQuery # Debug

# Invoke SQLCMD to get Table List, with Try Catch to handle any SQLCMD errors since Invoke-Sqlcmd doesn't have good error handling
try {
    $params = @{
        ConnectionString = $ConnectionString
        Query = $tableListAsPropertiesQuery
        IncludeSqlUserErrors = $true # Undocumented but required to get the actual error message from SQL Server
        ErrorAction = 'Stop'
    }

    $queryResult = Invoke-Sqlcmd @params
    # Write-Host $queryResult[0] # Debug
    $tableListAsProperties = $queryResult[0]
    if($tableListAsProperties.length -eq 0) {
        Write-Host("No tables returned from Table List Query")
        exit 1 # Exit with error code for GitHub actions to stop the workflow
    }
    Write-Host "Table List Query complete"
    $tableListAsParameters = $tableListAsProperties -split ','
    # Write-Host "Table Parameters: $tableListAsParameters" # Debug
}
catch {
    Write-Host("Error querying database for Table List:" + $_.Exception.Message)
    exit 1 # Exit with error code for GitHub actions to stop the workflow
}

# SqlPackage Extract https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-extract?view=sql-server-ver16
# Full Schema + selected table data
Write-Host "Starting SqlPackage Extract"
SqlPackage /Action:Extract /TargetFile:"$fullFilePath" /SourceConnectionString:"$ConnectionString" /p:VerifyExtraction=true $tableListAsParameters | Write-Host

Write-Host "Export complete"
Write-Output $fullFilePath

2 - backup-database.yml

  backup-database:
    runs-on: windows-latest

    steps:
      - uses: actions/checkout@v4
        with:
          sparse-checkout: 'SourceCode/Database/BackupScripts'

      - name: Ensure SqlPackage is installed
        shell: pwsh
        run: dotnet tool install -g microsoft.sqlpackage

      - name: Create Backup Directory
        shell: pwsh
        run: New-Item -Path ./SourceCode/Database/BackupScripts/Backup -ItemType Directory -Force

      - name: Run Backup Script
        shell: pwsh
        run: ./SourceCode/Database/BackupScripts/BackupDatabase.ps1 -DatabaseName "${{ env.DatabaseName }}" -ConnectionString "${{ secrets.DATABASE_CONNECTION_STRING_RTLC }}" -ExludeTableNames "Log4net,LogEvent,__MigrationHistory" -DacpacOutputPath "${{ github.workspace }}/SourceCode/Database/BackupScripts/Backup"

      - name: Upload database for upload job
        uses: actions/upload-artifact@v3
        with:
          name: .database-backup
          path: ./SourceCode/Database/BackupScripts/Backup

3 - RestoreDatabase.ps1

# Restores a local dacpac file to a SQL Server database
# IMPORTANT: Uses CreateNewDatabase=True to ALWAYS creates a new database, an existing database will be dropped and recreated.
# The connection string should include the "initial catalog=rtlc-prod-copy;" to specify the target database name.

# Example Usage:
# .\RestoreDatabase.ps1 -DatabaseConnectionString "data source=.\SQLEXPRESS;initial catalog=rtlc-prod;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;TrustServerCertificate=True" -DacpacInputFilePath "C:\Users\joshh\AppData\Local\Temp\rtlc-prod_2024-04-07.11-22-51.dacpac"

# Target Local Database & Parameters
param (
    [Parameter(Mandatory = $true)]
    [string]$DatabaseConnectionString,
    [Parameter(Mandatory = $true)]
    [string]$DacpacInputFilePath
)

# Check if the dacpac file exists
if(-not (Test-Path $DacpacInputFilePath)) {
    Write-Host("Dacpac file not found at $DacpacInputFilePath")
    exit 1 # Exit with error code
}

# SqlPackage Publish https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16
SqlPackage /Action:Publish /SourceFile:`"$DacpacInputFilePath`" /TargetConnectionString:`"$DatabaseConnectionString`" /p:CreateNewDatabase=True | Write-Host

Write-Host "Import complete"

4 - BackupRemoteProdAndRestoreLocal.ps1

# Execute the Backup Script against the Production database
# Initial Catalog in the connection string is the actual database name
Write-Host "Backing up the Production database and Restoring to Local database"
$BackupResultFileName = .\BackupDatabase.ps1 -DatabaseName "rtlc-prod" `
    -ConnectionString "ProductionConnectionStringSecret" `
    -ExludeTableNames "Log4net,LogEvent,__MigrationHistory" `
    -DacpacOutputPath "C:\Users\joshh\AppData\Local\Temp"

# Check if the Backup was successful
Write-Host "Backup Result Output: $BackupResultFileName"
if ($BackupResultFileName.length -eq 0) {
    Write-Host("Error backing up the database")
    exit 1 # Exit with error code
}

# Restore the Production database to the Local database
# IMPORTANT: This will overwrite the existing local database specified in the Initial Catalog of the connection string
Write-Host "Restoring $BackupResultFileName to Local database"
& .\RestoreDatabase.ps1 -DatabaseConnectionString "data source=.\SQLEXPRESS;initial catalog=rtlc-prod;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework;TrustServerCertificate=True" `
    -DacpacInputFilePath $BackupResultFileName

Write-Host "Database Restore Complete"