Powershell 3.0 and SQL 2012 not scripting correctly

by tea0819 at 2012-12-08 09:11:37

Hello. I have a powershell script that sits on a Central Management Server (CMS). This script goes out to all of our SQL Server instances in our environment and scripts out all of tables on a weekly basis. The tables script that is generated includes Primary Keys, Indexes, and Default constraints. This has been working fine for several years. Our CMS was SQL Server 2008R2 and we were running Powershell 2.0.

We recently upgraded the server to SQL Server 2012 and Powershell 3.0, and now the table scripts only contain the table structure, but no Primary Key declarations, no indexes, and no default constraints.

Nothing has changed in the script itself so all of the scripting options are still the same. Is anyone aware of any changes with Powershell 3.0 that i would have to make for my scripting to work properly?? Thank In Advance!!

Im on twitter if you would like to contact me there: @PrimeTimeDBA
by cmille19 at 2012-12-08 13:12:37
Are you explicitly setting scripting options?
by tea0819 at 2012-12-08 15:45:42
Yes. Ive always used the DriAll = $True setting which gave me PK’s, indexes, and defaults. Even when i set DriIndexes, ClusteredIndexes, NonClusteredIndexes, etc to $True, it still does not script them out…
by cmille19 at 2012-12-09 16:52:16
Can you share the relevant portion of your script? I’d like to try and repro issue with SQL Server 2012 vs. SQL Server 2008 R2.
by tea0819 at 2012-12-14 07:25:51
You can view the script at http://bit.ly/YRn325
by cmille19 at 2012-12-15 11:16:18
You mentioned you upgraded your CMS from 2008 R2 to 2012. Was this an in place upgrade? If so you’ll have both 2008/2008 R2 and 2012 SMO assemblies on the same machine. Since you’re using deprecated LoadWithPartialName method to load assemblies this could be an issue with loading incompatible assembles. I would suggest using strongly typed assembly names to ensure you’re loading the correct ones. Here’s an example I use in my script using supported add-type:

try {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop}
catch {add-type -AssemblyName “Microsoft.SqlServer.ConnectionInfo”}

try {add-type -AssemblyName “Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop; $smoVersion = 10}
catch {add-type -AssemblyName “Microsoft.SqlServer.Smo”; $smoVersion = 9}

try {add-type -AssemblyName “Microsoft.SqlServer.SMOExtended, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91” -EA Stop}
catch {add-type -AssemblyName “Microsoft.SqlServer.SMOExtended” -EA Stop}
catch {Write-Warning “SMOExtended not available”}