Iris Classon
Iris Classon - In Love with Code

How to script/export data and or schema from MSSQL tables

Although I’m sure this is something a lot of developers know and have done before I’m adding this post as a how-to for some of the consultants for our product. Not all of them are used to working with SQL and our beta setup requires some manual work.

At the moment we have to manually configure the database for our ’trial’ tenants that are running the pre-release version of the system. Depending on the type of setup they will have we copy parts of the schema, and data, from a combination of QA tenants. Until we have a set base template we have to generate the script manually, something that fortunately is straight forward even for for non- developers or database administrators. You can, of course, also use PowerShell to create a script that will generate the script. For example:

 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') 

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"  

$databases =$server.Databases 

$database = $databases["Personal"]

# To only script database:

# $database.Script()

$transferScript = new-object ("Microsoft.SqlServer.Management.Smo.Transfer") $database

$transferScript.Options.ScriptData = $true;

$transferScript.EnumScriptTransfer()

However, if you prefer the clicky way,- then this is how you generate schema and/or data scripts:

sql-scripting-data-and-or-schema5

 

Comments

Leave a comment below, or by email.


Last modified on 2017-02-02

comments powered by Disqus