Recently I needed to export table data from a Sql Server backup. Since Cloud Sql for Sql Server has no feature for this, this blog shows how to export tables using a Cloud Build job.
Understanding The Extraction Job
The export process takes a Sql Server backup (BAK-file), restores the backup and queries the database to export all table data. The table data, finally, is copied to Cloud Storage for further usage.
Cloud Build Job Implementation
source:
repoSource:
projectId: your-google-project
repoName: cloudbuild
branchName: main
steps:
# Copy the backup to the Cloud Build instance
- name: 'google/cloud-sdk:slim'
entrypoint: gsutil
args: [ 'cp', '${_GCS_BACKUP_FILE}', '/workspace/snapshot.bak' ]
# Run a Sql Server instance to facilitate the export
- name: gcr.io/cloud-builders/docker
entrypoint: bash
args:
- '-c'
- |
docker run -d --name mssql --net cloudbuild \
-e "ACCEPT_EULA=Y" \
-e "MSSQL_PID=Express" \
-e "SA_PASSWORD=S3cuReMe!N0W" \
-v "/workspace:/workspace" \
-d mcr.microsoft.com/mssql/server:2019-latest
n=0
while :
do
curl -s -S -v telnet://mssql:1433 && break
if [[ $n -lt 5 ]]; then
((n++))
echo "Command failed. Attempt $n/5:"
sleep 5;
else
echo "The command has failed after 5 attempts."
exit 1
fi
done
# Restore the database
- name: 'mcr.microsoft.com/powershell:lts-7.2-ubuntu-20.04'
entrypoint: pwsh
args:
- '-nologo'
- '-l'
- '-c'
- >
Write-Host "Installing SqlServer module.."
Install-Module -Name SqlServer -Force
Write-Host "Restoring database.."
$$Moves = Invoke-Sqlcmd -ServerInstance mssql -Username SA -Password S3cuReMe!N0W -Query "RESTORE FILELISTONLY FROM DISK = '/workspace/snapshot.bak'" `
| ForEach-Object { "MOVE '" + $$_.LogicalName + "' TO '/var/opt/mssql/data/" + (Split-Path $$_.PhysicalName -Leaf) + "'" } `
| Join-String -Separator ", "
Invoke-Sqlcmd -ServerInstance mssql -Username SA -Password S3cuReMe!N0W -Query "RESTORE DATABASE snapshot FROM DISK = '/workspace/snapshot.bak' WITH $$Moves"
# Extract database table data
- name: 'mcr.microsoft.com/powershell:lts-7.2-ubuntu-20.04'
entrypoint: pwsh
args:
- '-nologo'
- '-l'
- '-c'
- >
New-Item -Path /workspace -Name "export" -ItemType "directory"
Write-Host "Installing SqlServer module.."
Install-Module -Name SqlServer -Force
Write-Host "Extracting database tables.."
$$Tables = Invoke-Sqlcmd -ServerInstance mssql -Username SA -Password S3cuReMe!N0W -Query "SELECT table_schema+'.'+table_name as [Table] FROM snapshot.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'" `
| ForEach-Object { $$_.Table }
foreach ($$Table in $$Tables)
{
Write-Host "Extracting table: $$Table.."
Invoke-Sqlcmd -ServerInstance mssql -Username SA -Password S3cuReMe!N0W -Query "select * from snapshot.$$Table" `
| Export-Csv (Join-Path -Path "/workspace/export" -ChildPath "$$Table.csv")
}
# Copy table data to destination bucket
- name: 'google/cloud-sdk:slim'
entrypoint: gsutil
args: [ '-m', 'cp', '/workspace/export/*', '${_GCS_DESTINATION_PATH}' ]
timeout: 1800s
Cloud Build Job Usage
Deploy the Cloud Build trigger as specified above. Associate a service account with permissions to read from the source bucket (roles/storage.objectViewer) and write to the destination bucket (roles/storage.objectAdmin). Finally, supply the _GCS_BACKUP_FILE
and _GCS_DESTINATION_PATH
parameters to restore the input backup file to the destination bucket.
Note that you’ll need a Cloud Source Repository to configure Manual triggers. Just create an empty cloudbuild
source repository for this purpose.
Discussion
This job implementation runs an Express version of Sql Server. Be aware of the limitations of this version. For larger databases (10GB+) you’ll need a Web/Standard or Enterprise license. I’d consider using Cloud Sql for those scenarios and implement a idle monitor to stop the database server after n minutes of inactivity.
In this case, the tables are exported as CSV-files. These files don’t contain column metadata. If you need this, simply alter the export to export a data and schema file. For alternative file formats, consider a different export tool.
Conclusion
Start exporting your Sql Server table data using Cloud Build jobs by reusing this template. Feel free to alter the template to match your needs (output formats) or scenario (larger databases, incremental exports).