How to dump an entire SQL Server 2014 database into a file, to be imported into a Postgres database?

Question

I have a SQL Server 2014 database from which I need to dump just the table data (no indexes, stored procedures, or anything else).

This dump needs to be imported into a Postgres 9.3 database "as-is".

What id the proper command line to create such a dump?

What does *entire sql server database* mean? How many tables, which size? Are there BLOBs? — Shnugo, Sep 15 '16 at 19:02
Have you tried basics? (1) [Export](http://stackoverflow.com/questions/14212641/export-table-from-database-to-csv-file) (2) [Import](http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table) — Kamil Gosciminski, Sep 15 '16 at 19:02
no blobs. about 1600 tables. about 8 GB total data volume. just need the data from all the tables, without any indexes, constraints, keys, etc... — Eugene Goldberg, Sep 15 '16 at 19:04
I'm curious: Thx for the acceptance... Are you really going to do it this way? — Shnugo, Sep 15 '16 at 19:38
This database, which I need to dump, is a remote database, and I will not be allowed to access it with SSMS. I just need a way to script a full dump, bring that over to my side, and import that dump into a Postgres db — Eugene Goldberg, Sep 15 '16 at 19:47

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

I must admit, this is more sort of a joke... You should rather follow the hint to use "Export" and write this to some kind of CSV. Just for fun:

EDIT: create a column list to avoid binary columns...

columns, which are not directly convertible into XML RAW are added with "invalid data":

DECLARE @Commands TABLE(ID INT IDENTITY,cmd NVARCHAR(MAX));

INSERT INTO @Commands(cmd)
SELECT '(SELECT TOP 3 ' 
                        + STUFF(
                            (
                                SELECT ',' + QUOTENAME(COLUMN_NAME)
                                FROM INFORMATION_SCHEMA.COLUMNS AS c 
                                WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
                                  AND c.DATA_TYPE NOT IN('image','text') AND c.DATA_TYPE NOT LIKE '%BINARY%'
                                FOR XML PATH('')
                            ),1,1,''
                          )
                        + 
                            (
                                SELECT ',''invalid data'' AS ' + QUOTENAME(COLUMN_NAME)
                                FROM INFORMATION_SCHEMA.COLUMNS AS c 
                                WHERE c.TABLE_CATALOG=t.TABLE_CATALOG AND c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
                                  AND (c.DATA_TYPE IN('image','text') OR c.DATA_TYPE LIKE '%BINARY%')
                                FOR XML PATH('')
                            )

                        + ' FROM '  + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.'
       + QUOTENAME(t.TABLE_NAME) + ' FOR XML RAW,TYPE) AS ' + QUOTENAME(t.TABLE_CATALOG + '_' + t.TABLE_SCHEMA + '_' + t.TABLE_NAME)  
FROM INFORMATION_SCHEMA.TABLES AS t WHERE t.TABLE_TYPE='BASE TABLE';

DECLARE @finalCommand NVARCHAR(MAX)=
(
    SELECT 'SELECT '
          +'(SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES FOR XML RAW,TYPE) AS ListOfTables'
          + (
                SELECT ',' + cmd
                FROM @Commands
                ORDER BY ID
                FOR XML PATH('')
            )
          + ' FOR XML PATH(''AllTables'')'
);

EXEC( @finalCommand);

score 0 · Answer 2 · answered May 25 '17 at 16:49

Microsoft recently announced a new command line tool mssql-scripter (it's open source and multi-OS) that allows you to generate T-SQL scripts for databases/database objects as a .sql file. The announcement is here.

Once launching the scripter you'll want to run a command similar to the following:

$ mssql-scripter -S serverName -U userName -d databaseName --data-only

More details are on the GitHub page usage guide: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md

How to dump an entire SQL Server 2014 database into a file, to be imported into a Postgres database?

2 Answers2

EDIT: create a column list to avoid binary columns...

Linked