Copy whole SQL Server database into JSON from Python

Question

I facing an atypical conversion problem. About a decade ago I coded up a large site in ASP. Over the years this turned into ASP.NET but kept the same database.

I've just re-done the site in Django and I've copied all the core data but before I cancel my account with the host, I need to make sure I've got a long-term backup of the data so if it turns out I'm missing something, I can copy it from a local copy.

To complicate matters, I no longer have Windows. I moved to Ubuntu on all my machines some time back. I could ask the host to send me a backup but having no access to a machine with MSSQL, I wouldn't be able to use that if I needed to.

So I'm looking for something that does:

db = {}
for table in database:
    db[table.name] = [row for row in table]

And then I could serialize db off somewhere for later consumption... But how do I do the table iteration? Is there an easier way to do all of this? Can MSSQL do a cross-platform SQLDump (inc data)?

For previous MSSQL I've used pymssql but I don't know how to iterate the tables and copy rows (ideally with column headers so I can tell what the data is). I'm not looking for much code but I need a poke in the right direction.

Answer 1

Have a look at the sysobjects and syscolumns tables. Also try:

SELECT * FROM sysobjects WHERE name LIKE 'sys%'

to find any other metatables of interest. See here for more info on these tables and the newer SQL2005 counterparts.

Answer 2

As an aside to your coding approach - I'd say :

• set up a virtual machine with an eval on windows
• put sql server eval on it
• restore your data
• check it manually or automatically using the excellent db scripting tools from red-gate to script the data and the schema
• if fine then you have (a) a good backup and (b) a scripted output.

Answer 3

I've liked the ADOdb python module when I've needed to connect to sql server from python. Here is a link to a simple tutorial/example: http://phplens.com/lens/adodb/adodb-py-docs.htm#tutorial

Answer 4

I know you said JSON, but it's very simple to generate a SQL script to do an entire dump in XML:

SELECT  REPLACE(REPLACE('SELECT * FROM {TABLE_SCHEMA}.{TABLE_NAME} FOR XML RAW', '{TABLE_SCHEMA}',
                        QUOTENAME(TABLE_SCHEMA)), '{TABLE_NAME}', QUOTENAME(TABLE_NAME))
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA
       ,TABLE_NAME