Using Python to Automate "Generate Scripts" option in Management Studio to get the data from local SQL Server and save it in a .sql file

Question

I want to use Python to automate the "Generate Scripts" option in Management Studio so that I can get the data from tables which are stored on local SQL Server and save it into a .sql file.

I want to save it in an SQL file so that later another user can access and verify the data on SSMS, even if he does not have access to the local SQL Server.

Thanks! Any help would be appreciated as I have been trying this for sometime now.

I tried using dataframes to fetch the data from SQL Server and then writing it into a csv file. Then I am not able to store it in a .sql file from csv.

Answer 1

mssql-scripter does exactly what you want. This is a free and MIT licensed command line tool by Microsoft to export data and/or schema of a database. It can generate backward compatible scripts to older versions of SQL Server. The output script can be run in SSMS.

The following command line dumps the data from a database to dump_file.sql in a SQL Server 2008 compatible format.

python -m mssqlscripter -S <hostname> -d <databasename> -U <user> -P <password> -f .\dump_file.sql --data-only --target-server-version 2008

If you want to use the mssqlscripter from within a python script the following snippet does the same thing as the above command line.

import mssqlscripter.main as scripter

scripter.main(['-S', 'hostname',
               '-d', 'databasename',
               '-U', 'user',
               '-P', 'password',
               '-f', r'.\dump_file.sql',
               '--data-only',
               '--target-server-version', '2008'])

Further useful options are available, see the builtin help with

python -m mssqlscripter -h