Export Microsoft SQL Database Data as Text

I know that some database clients already provide this functionality where you can export their data into text files or other textual formats, but in my case, the data resided on Microsoft SQL Server 2005 and the only GUI client available was SQL Server Management Studio Express, which misses this basic feature.

After some investigation I managed to use some scripts to extract the data from all tables within a specific database into text files. Two command line utilities helped me here; one sqlcmd and the other bcp. These tools should already exist in your installation folder if you have Microsoft SQL Server installed.

I’m going to explain here the steps. Let’s assume the database I’m trying to export is called “mydb”:

  1. Created a text file called “mydb.sql” that contains sqlcmd commands for extracting list of tables in “mydb” database. This file contains:

    use mydb;
    go
    select name from sysobjects;
    go

    The select statement above is like any other SQL select. So it can have a where clause to filter out tables in the “mydb” database.

  2. Created a batch file called “mydb-tables.bat” that calls the above query to extract the desired tables. Here’s what’s in this file:

    sqlcmd -i mydb.sql -o mydb.out

    This batch file when run stores the outcome of the query in step #1 in “mydb.out”.

  3. Copied the list of returned table names from “mydb.out” and used an online line-break remover tool (such as this) to convert the new lines into white spaces. The result should have this format:

    Table1 Table2 Table3 … Tablen

  4. Created another batch file called “mydb.bat” that extracts content of these tables one by one. Here’s the content of this file:

    FOR %%A IN (Table1 Table2 … Tablen) DO bcp mydb.dbo.%%A out .\mydb\%%A.txt -U <username> -P <password> -t \t -r \n -w >> mydb.log

    Note that:
    – Inside the brackets is the list of tables extracted in step #3;
    – out refers to where the output should be created;
    – It is assumed that you have a “mydb” folder inside the current directory that will hold the extracts after this script is called;
    <username> and <password> are valid credentials for accessing “mydb” database;
    -t \t indicates a tab character should separate columns in the result;
    -r \n indicates that a new line character should separate rows in the result; and
    -w indicates that data in the database is copied using unicode characters.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: