Powershell SMO: This method does not support scripting data

Question

I want to generate an import script for a MSSQL DB via Powershell (related to this question). I tried doing this:

#Set-ExecutionPolicy RemoteSigned

$DB_NAME = "<<dbName>>"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "<<server>>"
$conContext = $srv.ConnectionContext
$conContext.LoginSecure = $false
$conContext.Login = "<<user>>"
$conContext.Password = "<<password>>"
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conContext)
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")

$db = $srv.databases[$DB_NAME]

$scripter = new-object "Microsoft.SqlServer.Management.Smo.Scripter" $srv

$scripter.Options.ScriptSchema = $false
$scripter.Options.ScriptData = $true
$scripter.Options.ScriptDrops = $false

$scripter.Script($db)

But executing this throws an error:

"This method does not support scripting data"

I also tried to set the output file option but this doesn't change anything.

Can you tell me what I did wrong?

Thanks!

Jeroen Mostert · Accepted Answer · 2016-07-29 12:31:31Z

5

Per the error, Scripter.Script does not support scripting data. This is documented. What isn't documented is what you're supposed to use instead, but it's EnumScript:

$scripter.EnumScript(@($db.Tables))

You must pass the tables, since simply scripting the database will yield nothing (as, technically, the database itself contains no data, its tables do).

(The @() forcibly converts the Tables collection to an array, since that's what EnumScript expects.)

answered Jul 29, 2016 at 12:31

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

C.M. Over a year ago

Hi and thanks for your great answer! This sounds like it could solve my problem. Unfortunately now I get this error: Ausnahme beim Aufrufen von "EnumScript" mit 1 Argument(en): "Script failed for Server 'xxx'. " In ... + $scripter.EnumScript(@($db.Tables)) + ~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FailedOperationException Do you know what causes this exception? Greetings!

Jeroen Mostert Over a year ago

No, and it has nothing to do with EnumScript itself -- I know because I verified this code works against a reachable database instance.

C.M. Over a year ago

Hi - I fugured out what went wrong: I want to use to set $scripter.Options.ScriptSchema = $false but without the schema I get this error. Is there a way to just script the data? And I need to order the tables in a way that there are no conflicts with foreign keys. Currently the tables are scripted alphabetically but this will throw errors when importing them into an empty DB...

Jeroen Mostert Over a year ago

@C.M. Sounds like you've got some more questions... feel free to post them. Comments aren't the way to go for that. You may want to make the question more general to prevent everything from devolving into an individual debugging session.

ViRuSTriNiTy Over a year ago

+1, Super helpful, replacing Script() with EnumScript() allows scripting objects as individual files with data.

Collectives™ on Stack Overflow

Powershell SMO: This method does not support scripting data

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related