Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output files have always included the table definition along with constraints, primary keys, and indexes.

We recently upgraded our CMS to Powershell 3.0 and SQL Server 2012 and now our output scripts have table definition only. The indexes, constraints, and key definitions are no longer part of the script. Does anyone know what has changed with SMO in this SQL Server 2012 release and how I can resolve my issue.

My code is below.

# LoopInstances

$InstanceListFile = "C:\temp\serverlist.txt"
$CurrentDay = Get-Date -Format dd
$InstanceList = Get-Content $InstanceListFile

foreach ($InstanceName in $InstanceList)
{
    if (($InstanceName -ne 'Server1') -and ($InstanceName -ne 'Server2'))
    {
    # Configuration
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null

    $MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

    $InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

    $RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" "$InstanceName"

    # The SetDefaultInitFields method tells the SMO API to add the IsSystemObject property to the default list for this session

    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.ExtendedStoredProcedure", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.StoredProcedure", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.Table", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.View", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.UserDefinedFunction", "IsSystemObject")
    $MyScripter.Server = $InstanceObject
    $ScripterOptions = $MyScripter.Options

    # SetScriptOptions

    $ScripterOptions.ToFileOnly = $True
    $ScripterOptions.AppendToFile = $True
    $ScripterOptions.ContinueScriptingOnError = $True
    $ScripterOptions.LoginSid = $True
    $ScripterOptions.AgentAlertJob = $True
    $ScripterOptions.AgentNotify = $True
    $ScripterOptions.Bindings = $True
    $ScripterOptions.DriAll = $True
    $ScripterOptions.Indexes = $True
    $ScripterOptions.Permissions = $True
    $ScripterOptions.SchemaQualify = $True
    $ScripterOptions.Triggers = $True
    $ScripterOptions.IncludeIfNotExists = $True
    $ScripterOptions.ExtendedProperties = $True

    # SetSchemaPath


    $SchemaPath = "C:\Temp\" + $CurrentDay + "\" + $InstanceName
    if ((Test-Path $SchemaPath) -ne $True) {mkdir $SchemaPath | Out-Null} 

    # DisplayInstance

    Write-Host "------------------------------------------------------------"
    Write-Host $InstanceName - (Get-Date)
    Write-Host "------------------------------------------------------------"

    # CheckVersion

    if ($InstanceObject.Information.VersionString.substring(0,2) -eq 10)
    {

        $InstanceObjectList = "Audits","BackupDevices","Credentials","CryptographicProviders","Databases","EndPoints","LinkedServers","Logins","ServerAuditSpecifications","Triggers","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","ProxyAccounts","SharedSchedules","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Assemblies","AsymmetricKeys","Certificates","DatabaseAuditSpecifications","Defaults","ExtendedProperties","FullTextCatalogs","FullTextStopLists","PartitionFunctions","PartitionSchemes","PlanGuides","Roles","Rules","Schemas","Synonyms","Triggers","UserDefinedAggregates","UserDefinedDataTypes","UserDefinedTableTypes","UserDefinedTypes","Users","XmlSchemaCollections"
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 9)
    {
        $InstanceObjectList = "BackupDevices","Credentials","Databases","EndPoints","LinkedServers","Logins","Triggers","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","ProxyAccounts","SharedSchedules","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Assemblies","AsymmetricKeys","Certificates","Defaults","ExtendedProperties","FullTextCatalogs","PartitionFunctions","PartitionSchemes","Roles","Rules","Schemas","Synonyms","Triggers","UserDefinedAggregates","UserDefinedDataTypes","UserDefinedTypes","Users","XmlSchemaCollections"
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 8)
    {
        $InstanceObjectList = "BackupDevices","Databases","LinkedServers","Logins","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Defaults","ExtendedProperties","FullTextCatalogs","Roles","Rules","UserDefinedDataTypes","Users"
    }

    # ScriptInstance

    Write-Host Instance
    foreach($Object in $InstanceObjectList)
    {
        Write-Host "    " $Object


        $ScripterOptions.FileName=$SchemaPath + "\Instance_" + $Object + ".sql"


        if ((Test-Path $ScripterOptions.FileName) -eq $True)
        {
            del $ScripterOptions.FileName
        }


        $InstanceObjectCombined = $InstanceObject.$Object
        $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectCombined)


        if ($? -eq $False)
        {
            Write-Host "FAILED - SCRIPT ABORTED"
        }
    }

        # ScriptJobServer

    if ($InstanceObject.Information.Edition -ne 'Express Edition')
    {
        Write-Host JobServer
        foreach($Object in $JobServerObjectList)
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\JobServer_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $InstanceObjectJobServer = $InstanceObject.JobServer.$Object
            $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectJobServer)
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
        }
    }


        # ScriptDatabases

    $DatabaseFilter = $InstanceObject.Databases | where {$_.Name -ne "distribution"} | where {$_.Name -ne "master"} | where {$_.Name -ne "msdb"} | where {$_.Name -ne "tempdb"}
    foreach($Database in $DatabaseFilter)
    {
        Write-Host $Database
        foreach($Object in $DatabaseObjectList)
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\Database_" + $Database.name + "_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $ScripterOptions.Permissions = $True
            $InstanceObjectDatabases = $InstanceObject.Databases[$Database.name].$Object
            $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectDatabases)
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
        }

        foreach($Object in "ExtendedStoredProcedures","StoredProcedures","Tables","Views","UserDefinedFunctions")
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\Database_" + $Database.name + "_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $ObjectFilter = $InstanceObject.Databases[$Database.name].$Object | where {$_.IsSystemObject -eq $False}
            if ($ObjectFilter)
            {
                $ScripterOptions.Permissions = $True
                $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$ObjectFilter)
                if ($? -eq $False)
                {
                    Write-Host "FAILED - SCRIPT ABORTED"
#                   exit 1
                }
            }
        }
    }

    # ScriptReplication - Added after all SMO scripting to avoid bug detected in losing the database context

    $RepScriptOptions = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions

    Write-Host Replication
    foreach($ReplicatedDatabase in $RepInstanceObject.ReplicationDatabases) 
    {
        $ScriptFileName=$SchemaPath + "\Replication_" + $ReplicatedDatabase.Name + ".sql"
        if ((Test-Path $ScriptFileName) -eq $True)
        {
            del $ScriptFileName
        }
            foreach($Publication in $ReplicatedDatabase.TransPublications) 
            {
            Write-Host "    " $Publication.Name
                $Publication.Script($RepScriptOptions) >> $ScriptFileName
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
            }
            foreach($Publication in $ReplicatedDatabase.MergePublications) 
            {
            Write-Host "    " $Publication.Name
                $Publication.Script($RepScriptOptions) >> $ScriptFileName
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
            }
    }
}
share|improve this question
1  
Have you tried adding a version check SQL 2012? You're bypassing an important step in your script. In other words: if ($InstanceObject.Information.VersionString.substring(0,2) -eq 11) –  Robert L Davis Dec 9 '12 at 20:30
    
Non of the environments that we are scripting from is 2012 yet. They are all 2008 or 2008R2. The management server, which this scripts runs from, is the only one that is 2012 as of yet. –  Tamarick Hill Dec 9 '12 at 20:34
    
But yes, you are correct, and that line of code will need to be added as we upgrade our other servers in the coming months... –  Tamarick Hill Dec 9 '12 at 20:35
1  
I see others reporting the same behavior and the consensus seems to be that it is an incompatibility between the SMO versions. Not sure that is correct. Maybe open a Connect item. –  Robert L Davis Dec 9 '12 at 21:05
    
I added the version check for 11 and ran it against a SQL 2012 instance and it still doesn't work. Not convinced it is a version incompatibility. –  Robert L Davis Dec 9 '12 at 21:39
show 2 more comments

1 Answer 1

I'm unable to reproduce the issue you describe. Here's simplified script I used to test from 2012 and 2008 R2 to a 2008 R2 server. Comment/Uncomment the add-type section as needed. One thought is that using deprecated LoadWithPartialName could be causing issues if you have both 2008 and 2012 assemblies on same machine.

#SQL 2008 R2
#add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
#add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#SQL 2012
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$InstanceName = "myservername"

$query = @"
CREATE TABLE dbo.authors(
    au_id int NOT NULL,
    au_lname varchar(40) NOT NULL,
    au_fname varchar(20) NOT NULL

 CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED 
(
    au_id ASC
)
) 
"@

$InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

$db = $InstanceObject.Databases['tempdb']
$db.ExecuteNonQuery($query)

$MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

$MyScripter.Server = $InstanceObject

$MyScripter.Options.DriAll = $true
#$MyScripter.Options.DriPrimaryKey = $false
#$MyScripter.Options.DriClustered = $false

$MyScripter.Script($InstanceObject.Databases['tempdb'].Tables['authors'])
share|improve this answer
    
Thanks! You answer helped me. In my case there were no constraints scripted JUST because I didn't explicitly set assemblies (I was using PartialName). –  gahcep May 28 at 20:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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