Feb 022017
 
 February 2, 2017  Posted by at 9:22 am PowerShell, Uncategorized  Add comments

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

sql-scripting-data-and-or-schema1

sql-scripting-data-and-or-schema2

sql-scripting-data-and-or-schema3

sql-scripting-data-and-or-schema4

 

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

What is 12 + 6 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)