📦 Export MS-SQL Tape File to CSV

4 min readJan 3, 2020

Finally I got some task for a Data Engineer sake, Yeah! I’ve to import a backup file from MSSQL Tape file format (which appear to be obsolete pretty soon LOL) to Dataprep as CSV.

I will follow below guide to achieve my task.

How to Install SQL Server on a Mac

Here I'll show you how to get SQL Server up and running on your Mac in less than half an hour. And the best part is…

database.guide

How to Restore a SQL Server Database on a Mac using Azure Data Studio

Restoring a database is a piece of cake with Azure Data Studio. It's a similar process to doing it with SQL Server…

database.guide

But I will summarize in my own version. Because it’s not working LOL

Prerequisites

Azure Data Studio is a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.
MSSQL Server : I will use it via Docker container

docker pull microsoft/mssql-server-linux

3. Homebrew (for install SQL Server Command Line Tools for Mac)

/usr/bin/ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

4. SQL Server Command Line Tools for Mac

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew updatebrew install --no-sandbox microsoft/mssql-release/msodbcsql
brew install microsoft/mssql-release/mssql-tools

Step 1 : Run SQL Server

docker run -d --name sql_server -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=reallyStrongPwd123' -p 1433:1433 microsoft/mssql-server-linux

Step 2 : Create folder inside container

docker exec -it sql_server mkdir /var/opt/mssql/backup

Step 3 : Copy .bak file into Docker container

docker cp foo.bak sql_server:/var/opt/mssql/backup

Step 4 : Open Azure Data Studio

What so weird about other tutorial is they didn’t told me that User name is “sa” , Everyone knowing this intuitively? I don’t!

Server : localhost
User name : sa
Password : reallyStrongPwd123

Step 5 : Restore

Select foo.bak that we just copy into container

We’re 50% done! Now let’s export as CSV and import to Dataprep

Step 6 : Convert to CSV

I try export CSV from Azure Data Studio but I can’t find a way to do it so I’ll use SQL Server Command Line Tools for Mac instead…

sqlcmd -S localhost -U sa -P reallyStrongPwd123 -Q "SELECT * FROM dbo.Foo"

But! this error throw…

dyld: Library not loaded: /usr/local/lib/libodbc.2.dylib
  Referenced from: /usr/local/bin/sqlcmd
  Reason: image not found

I will need unixodbc

brew install unixodbc

But other error followed LOL

brew install unixodbc
Warning: unixodbc 2.3.7 is already installed, it's just not linked
You can use `brew link unixodbc` to link this version.katopz@kat git % brew link unixodbc     
Linking /usr/local/Cellar/unixodbc/2.3.7... 
Error: Could not symlink include/autotest.h
/usr/local/include is not writable.katopz@kat git % sudo chown -R $(whoami) /usr/local/include
chown: /usr/local/include: No such file or directorykatopz@kat git % mkdir /usr/local/include                  
mkdir: /usr/local/include: Permission deniedkatopz@kat git % sudo mkdir /usr/local/includekatopz@kat git % sudo chown -R $(whoami) /usr/local/includekatopz@kat git % brew link unixodbc                        
Linking /usr/local/Cellar/unixodbc/2.3.7... 37 symlinks created

I hope you fixed it all mess (see above for my journey), Now let’s try again

sqlcmd -S localhost -U sa -P reallyStrongPwd123 -Q "SELECT * FROM dbo.Foo"

Awww

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/usr/local/lib/libmsodbcsql.17.dylib' : file not found.

Fix it by symbolic link (Don’t ask me why this happen and how to prevent this LOL)

ln -s /usr/local/Cellar/msodbcsql17/17.4.2.1/lib/libmsodbcsql.17.dylib /usr/local/lib/libmsodbcsql.17.dylib

Awwwwwww

Invalid object name 'dbo.Foo'.

After 2 hours pass which didn’t make any sense error, I just realize that I need to specified which DB via USE statement, In my case is “BAR” I use so here is a working one!

sqlcmd -S localhost -U sa -P reallyStrongPwd123 -s, -W -Q "USE BAR SELECT * FROM dbo.DimDate" > foo.csv

This took me sometime to figure this out!

-s, The option defines the comma as a column separator.
-W To remove white space

Wow, I learn a lot from this task, I will do this again for linux flow just hope it could be easier LOL

To support content like this please feel free to tip me via 👉 PayPal Thanks!