I am creating a Powershell script to read all objects in a source database, script them and then re-create them in an empty destination DB (may be across different SQL Server versions). For the most part my script works well and has helped identify a few gotchas in some old procs and functions, but I cannot get it to script out CLR functions.

There are only one or two and if I right click on them in SSMS and create script to new query editor window it works fine and I can then run that on the destination DB and it creates the assembly, but when I try and use SMO in Powershell it just does not script the assemblies (it doesn't error either). In essence, this is my script (left out the bits which create the connections/DB objects etc):

Write-Host "Getting DB objects..."
$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  } 

# Set scripter options to ensure only schema is scripted
$scripter.Options.ScriptSchema  = $true;
$scripter.Options.ScriptData    = $false;

#Exclude GOs after every line
$scripter.Options.NoCommandTerminator   = $false;
$scripter.Options.ToFileOnly            = $false
$scripter.Options.AllowSystemObjects    = $false
$scripter.Options.Permissions           = $true
#$scripter.Options.DriAllConstraints     = $true
$scripter.Options.DriForeignKeys        = $false
$scripter.Options.SchemaQualify         = $true
$scripter.Options.AnsiFile              = $true

$scripter.Options.Indexes               = $true
$scripter.Options.DriIndexes            = $true
$scripter.Options.DriClustered          = $true
$scripter.Options.DriNonClustered       = $true
$scripter.Options.NonClusteredIndexes   = $true
$scripter.Options.ClusteredIndexes      = $true
$scripter.Options.FullTextIndexes       = $true

$scripter.Options.EnforceScriptingOptions   = $true

function CopyObjectsToDestination($objects) {

    foreach ($o in $objects) { 

        if ($o -ne $null) {
            try {
                Write-Host "Writing " $o.Name
                $script = $scripter.Script($o)

            } catch {
                #Make sure any errors are logged by the SQL job.
                $ex = $_.Exception
                $message = $ex.message
                $ex = $ex.InnerException

                while ($ex.InnerException) {
                    $message += "`n$ex.InnerException.message"
                    $ex = $ex.InnerException
                Write-Error $message

# Output the scripts
Write-Host "Create assemblies in destination database..."
CopyObjectsToDestination $assemblies

I feel like I'm missing something simple and obvious because I can get tables, procs, functions, views and the like without any problems at all. What have I missed?

  • Perhaps this is related to a specific combination of Powershell/SMO/SQL Server versions? This seems to be working for me on PS3 & SQL Server 2008 R2 SP2 – Ed Harper Apr 28 '14 at 14:39
  • Really? OK, I'll see what versions this is using on my machine. I have SQL 2008 R2 and 2012 installed but there's likely to be 2005 and 2008 components hanging about as it was someone else's before me. I'll try specifying a specific SMO version first. – Steve Pettifer Apr 28 '14 at 14:43
  • Are the assemblies definitely in $schema? Does running $sourceDb.Assemblies | Where-object { $_.schema -eq $schema } produce output? – Ed Harper Apr 28 '14 at 14:45
  • I think the schema filter is the issue. The SqlAssembly class doesn't have a schema property - it has owner instead - technet.microsoft.com/en-us/library/… – Ed Harper Apr 28 '14 at 14:50
  • Gah!! Of course! Assemblies don't live in schemas. I knew it had to be something simple - the copy and paste monster was at work. Stick that as an answer and I'll accept it because it's spot on, thank you. – Steve Pettifer Apr 28 '14 at 14:52

The SqlAssembly class doesn't have a schema property - so this filter is silently removing any potential results:

$assemblies     = $sourceDb.Assemblies | Where-object { $_.schema -eq $schema  } 

I'm not sure whether you need to use the owner property in the filter instead or ignore ownership entirely.

  • Thank you! I changed the filter to -not $_.IsSystemObject to ensure I only pick up user-created assemblies and it works as expected. Been driving me nuts all day! Annoyingly I must have looked at that BOL page a dozen times today and didn't twig. – Steve Pettifer Apr 28 '14 at 14:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.