Linux cli tool to dump SQL Server schema to a text file

Question

Do you know a reliable command line tool able to export SQL Server schema to a text file?

Caracaraballo

Answer 1

You can do this with mssql-scripter. Download through - pip install mssql-scripter.

The command you'll want to use is along the lines of:

$ mssql-scripter -S serverName -d databaseName -U user > ./my-schema.sql 

The default is schema only (you can also specify --schema-and-data and --data-only). The command line will prompt for your password.

And you can pipe to stdout, sed, or a .sql file currently. Here's the GitHub page as this is an OSS repo - https://github.com/Microsoft/mssql-scripter. Please do file issues on the repo if you run into any.

Catima

Answer 2

Because it's 2024 and it might be a challenge to run mssql-scripter (which is also deprecated by microsoft) you can still rely on an image from the guys at backplane: https://registry.hub.docker.com/r/backplane/mssql-scripter

Beware, if you want to avoid problems with special characters like Ä you must define an output with the -f ./myfile.sql option.

Also, if you're connecting to a sql server instance on your localhost, replace localhost with host.docker.internal

I use this command on macOS to get a data dump with mssql-scripter in 2024:

docker run \
  --rm \
  --interactive \
  --tty \
  --volume "$(pwd):/work" \
  "backplane/mssql-scripter" \
  --server host.docker.internal,1433 \
  --database my_db \
  --user useruseruser \
  --password MyPassword123 \
  --data-only \
  --display-progress \
  -f /work/data-dump.sql

We output the file to /work/data-dump.sql because /work is mounting our current directory on our host.

Pegpega