Skip to content

Instantly share code, notes, and snippets.

@jimmont jimmont/mssql.notes.txt
Last active Sep 20, 2018

Embed
What would you like to do?
mssql on macos setup notes
§ install
1. install Homebrew https://docs.brew.sh/Installation.html
2. install Docker
$ brew cask install docker
this places the Docker.app in the Applications folder, and can be launched directly to start the service
3. install mssql per the instructions https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker
$ sudo docker pull microsoft/mssql-server-linux:2017-latest
4. run the image, check the status
$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_PID=Developer' -e 'MSSQL_SA_PASSWORD=<password>' -p 1433:1433 --name mssql0 -d microsoft/mssql-server-linux:2017-latest
$ docker ps -a
$ netstat -an | grep LISTEN
$ sudo lsof -nPi -sTCP:LISTEN | grep 1433
5. install sql-cli and connect https://www.npmjs.com/package/sql-cli
$ npm install -g sql-cli
$ mssql -u sa -p <password>
or possibly something like
$ mssql -s <host> -u <username@host> -p <password> -d <database> -e
Connecting...Enter ".help" for usage hints.
mssql> .help
§ copy database
1. create backup on remote host
$ sqlcmd -H localhost -U SA -P <password> -Q "BACKUP DATABASE [<database>] TO DISK = N'<filename>' WITH NOFORMAT, NOINIT, NAME = '<name>', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
$ sudo mv /var/opt/mssql/data/<filename> ~/
2. copy backup to local container directory 'backup'
$ scp <user@host>:</path/to/filename> ~/
$ sudo docker exec -it mssql0 mkdir /var/opt/mssql/backup
$ sudo docker cp <filename> mssql0:/var/opt/mssql/backup
3. restore from backup
$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/<filename>"' | tr -s ' ' | cut -d ' ' -f 1-2
LogicalName PhysicalName
------------------------
<database> /var/opt/mssql/data/<database.mdf>
...multiple logical and physical names: for each add a MOVE logicalName TO physicalName entry like:
$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'RESTORE DATABASE <database> FROM DISK = "/var/opt/mssql/backup/<filename>" WITH MOVE "<logicalName1>" TO "<physicalName1>", MOVE "<logicalName2 /var/opt/...mdf>" TO "<physicalName2 /var/opt/...ldf>"'
Processed...
RESTORE DATABASE successfully processed...
4. make queries
$ sudo docker exec -it mssql0 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<password>' -Q 'SELECT Name FROM sys.Databases'
§ tasks
0. change master password
> ALTER LOGIN SA WITH PASSWORD='<password>'
1. setup readonly user with sql; in my case I either did something wrong or had a confused preexisting/restored user and to fix I removed the user (on both sys and db: drop user <username>) and recreated.
> create login <username> with password='<password>'
> create user <username> from login <username>
> grant connect, select, execute to <username>
> use <database>
> create user <username> from login <username>
> grant connect, select, execute to <username>
§ more info, links
* more Docker
https://docs.docker.com/get-started/
https://docs.docker.com/docker-for-mac/
* helpful
http://database.guide/how-to-install-sql-server-on-a-mac/
https://medium.com/@reverentgeek/sql-server-running-on-a-mac-3efafda48861
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-backup-and-restore-database
https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-to-other-servers
https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.