Blog

How to export Sql Server tables to Cloud Storage

30 Dec, 2022
Xebia Background Header Wave

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).

Image by annca from Pixabay

Laurens Knoll
As a cloud consultant I enjoy taking software engineering practices to the cloud. Continuously improving the customers systems, tools and processes by focusing on integration and quality.
Questions?

Get in touch with us to learn more about the subject and related solutions

Explore related posts