📦 Export MS-SQL Tape File to CSV

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.

Poor tape file ;p

I will follow below guide to achieve my task.

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

Prerequisites

  1. 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.
  2. MSSQL Server : I will use it via Docker container
docker pull microsoft/mssql-server-linux
Waiting…,If stuck try other hotspot!

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 update
brew 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

User name is sa and no one told me!

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

Click that Restore button
Select foo.bak that we just copy into container
It work!

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

WTF 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 directory
katopz@kat git % mkdir /usr/local/include
mkdir: /usr/local/include: Permission denied
katopz@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!

DLT & ML Debugger