Last active
April 7, 2024 18:13
-
-
Save jhelmink/b579ae0e1494b9b4ef497311c8a11754 to your computer and use it in GitHub Desktop.
Creates a dacpac file from a SQL Server database, with FULL SCHEMA and TABLE DATA, but optionally EXCLUDES specific table data
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment