📦 Export MS-SQL Tape File to CSV

katopz
katopz
Jan 3 · 4 min read

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

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.

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!

More From Medium

Related reads

Related reads

Related reads

Decision Tree

161

Also tagged Data Engineer

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade