[EN] Sunday Market–PowerShell Scripts by Idera

HeroShot_PowershellScriptsIt Sunday again. And some shops are closed. If Yes, it’s time to get next useful tool for free. it’s my fifth episode in non-frequently series: Sunday Market. This time we will look for Idera again. This time we will not install anything. Just download and run. For free download You must:
>> go to Idera PowerShell Scripts site HERE
>> fill registration data
>> checking Your mail box for download link

Easy. As always. Next – when You downloaded nat too large (82 MB) file – we can look inside. This special package conains compilation of scripts for: Sharepoint Server, SQL Server (2005 & 2008), Exchange Server, Active Directory and just for PowerShell Plus Trial Installators (for 32- and 64-bits). Lets look for all parts:

\IderaPowerShellScripts\Idera_ADPowerShellScripts\

Add-IADGroupMember.ps1 / Disable-IADObject.ps1 / Disable-UserCannotChangePassword.ps1 / Enable-IADObject.ps1 / Enable-UserCannotChangePassword.ps1 / Get-IADAltRecipient.ps1 / Get-IADComputer.ps1 / Get-IADCurrentDomain.ps1 / Get-IADCurrentForest.ps1 / Get-IADDomainController.ps1 / Get-IADDomainController2.ps1 / Get-IADDomainPasswordPolicy.ps1 / Get-IADEmptyGroup.ps1
/ Get-IADExchangeAdmin.ps1 / Get-IADFSMORoleHolder.ps1 / Get-IADGroup.ps1 / Get-IADGroupMember.ps1 / Get-IADGroupMembership.ps1 / Get-IADObjectBySID.ps1 / Get-IADObjectSID.ps1 / Get-IADSite.ps1 / Get-IADSubnet.ps1 / Get-IADTombstoneComputer.ps1 / Get-IADTombstoneObject.ps1 / Get-IADTombstoneUser.ps1 / Get-IADUser.ps1 / Move-IADObject.ps1 / New-IADGroup.ps1 / New-IADPassword.ps1 / New-IADUser.ps1 / Remove-IADGroupMember.ps1 / Remove-IADObject.ps1 / Set-IADUser.ps1

\Idera_ExchangeScripts\
Disable-IEXUser.ps1 / Get-IEXAvailableSpace.ps1 / Get-IEXClusterNode.ps1 / Get-IEXCurrentRole.ps1 / Get-IEXDatabaseQuotaStatistics.ps1 / Get-IEXDatabaseSize.ps1 / Get-IEXDatabaseStatistics.ps1 / Get-IEXEmailAddressOwner.ps1 / Get-IEXEmailAddressReport.ps1 / Get-IEXForwardMailbox.ps1 / Get-IEXInactiveMailbox.ps1 / Get-IEXLastSendAndReceivedDate.ps1 / Get-IEXMailboxAddressCount.ps1 / Get-IEXMailboxCountReport.ps1 / Get-IEXMailboxFolderStatistics.ps1 / Get-IEXMailboxStatistics.ps1 / Get-IEXSendAsPermission.ps1 / Get-IEXTip.ps1 / New-IEXAddressList.ps1 / New-IEXMailboxFromCsv.ps1 / New-IEXTestMailbox.ps1 / Purge-IEXMailbox.ps1 / Remove-IEXDisabledUser.ps1 / Set-IEXEmailAddress.ps1 / Test-IEXDistinguishedName.ps1 / Test-IEXEmailAddress.ps1 / Test-IEXEmailAddress2.ps1

\IderaPowerShellScripts\Idera_SharePointScripts\
Add-SPList.ps1 / Add-SPSite.ps1 / Add-SPUser.ps1 / CheckIn-SPDocument.ps1 / CheckOut-SPDocument.ps1 / Display-SPItemInformation.ps1 / Display-SPList.ps1 / Display-SPWebPart.ps1 / Export-SPSite.ps1 / Get-SPView.ps1 / Get-SPWeb.ps1 / Import-SPSite.ps1 / Remove-SPList.ps1 / Remove-SPSite.ps1 / Set-SPItem.ps1 / ++ Readme.doc & Readme.pdf

\IderaPowerShellScripts\Idera_SQLServerScripts\
for SQL Server 2005
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1List-MSSQL-PropertyInfo.ps1Load-MSSQL-SMO.ps1Start-SQLServerService.ps1Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

for SQL Server 2008
Backup-SSAS.ps1 / CheckDB-MSSQL-UsingADO.ps1 / Connect-MSSQL-IPSQLAuth.ps1 / Connect-MSSQL-IPWindowsAuth.ps1 / CreateDB-MSSQL-UsingSMO.ps1 / Create-MSSQLJob-UsingSMO.ps1 / Create-MSSQL-TestDb.ps1 / CreateTable-MSSQL-UsingSMO.ps1 / Get-MSSQL-DB-UsingADO.ps1 / Get-MSSQL-DB-UsingSMO.ps1 / Get-MSSQL-MaxMemory.ps1 / Get-MSSQL-Port-UsingDMO.ps1 / Get-MSSQL-Port-UsingWMI.ps1 / Get-MSSQL-ServerAttrib-Csv.ps1 / Get-MSSQL-ServerAttrib-Html.ps1 / Get-MSSQL-ServerRegisterations.ps1 / Get-MSSQL-Views-Csv.ps1 / Insert-MSSQL-SampleData-Csv.ps1 / List-MSSQL-FailedJobs.ps1 / List-MSSQL-PropertyInfo.ps1 / Load-MSSQL-SMO.ps1 / Start-SQLServerService.ps1 / Stop-SQLServerService.ps1 / ++SampleData.csv / ++Readme.doc & Readme.pdf

Interesting listing, right? Let’s run few examples below:

*** CHECKDB ***

## ======================================================

## Title       : CheckDB-MSSQL-UsingADO

## Description : Run a DBCC against specified server instance and database

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance <server\instance>

##               -dbName <database name>

##               -verbose

##               -debug             

## Output      :

## Usage                                              : PS> .\CheckDB-MSSQL-UsingADO -serverInstance local -dbName master -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, CheckDB

## Change log  :

## ======================================================

 

param

(

   [string]$serverInstance = “.”,

                [string]$dbName = “AdventureWorks”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CheckDBMSSQLUsingADO $serverInstance $dbName

}

 

function CheckDB-MSSQL-UsingADO($serverInstance, $dbName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                $cn = new-object system.data.SqlClient.SqlConnection( `

                               “Data Source=$serverInstance;Integrated Security=SSPI;Initial Catalog=$dbName”);

                $ds = new-object System.Data.DataSet “dsCheckDB”

                $query = “DBCC CHECKDB($dbName) WITH TABLERESULTS”

                $da = new-object “System.Data.SqlClient.SqlDataAdapter” ($query, $cn)

                $da.Fill($ds)

 

                $dtCheckDB = new-object “System.Data.DataTable” “dsCheckDB”

                $dtCheckDB = $ds.Tables[0]

                $dtCheckDB | Format-Table -autosize `

                               -property Error, Level, State, MessageText, `

                                                                                              RepairLevel, Status, DbId, ObjectId, `

                                                                                              IndexId, PartitionId, AllocUnitId, File, `

                                                                                              Page, Slot, RefFile, RefPage, RefSlot, Allocation

}

 

main

*** MAXMEMORY ***

## ======================================================

## Title       : Get-MSSQL-MaxMemory

## Description : Get max memory property from SQL Server

## Author      : Idera

## Date        : 9/1/2008

## Input       : -serverInstance           <server\instance>

##                                                            -verbose

##                                                            -debug

## Output      : Integer in MB,

##                                                                          -1 if memory is unlimited,

##                                                                          -2 if no connection to the desired DB can be obtained

## Usage                                              : PS> .\Get-MSSQL-MaxMemory -serverInstance MyServer -verbose -debug

## Notes                                               : Adapted from Jakob Bindslet script

## Tag                                   : SQL Server, ADO, Configuration, Memory

## Change log  :

## ======================================================

 

param

(

                [string]$serverInstance = “local”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                GetMSSQLMaxMemory $serverInstance

}

 

function Get-MSSQL-MaxMemory($serverInstance)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

 

                #initialize ADO connection parameters

                Write-Debug “Initializing connection parameters…”

                $adoOpenStatic = 3

                $adoLockOptimistic = 3

                $timeout = 3

 

                #get memory configuration setting from sysconfigures system table

                # TIP: using ADO to run a query

                Write-Debug “Connecting to server: $serverInstance”

                $adoConnection = New-Object -comobject ADODB.Connection

                $adoRecordset = New-Object -comobject ADODB.Recordset

                $query = “SELECT value FROM [master].[dbo].[sysconfigures] WHERE config = 1544”

                $adoConnection.Set_ConnectionString(“Provider=SQLOLEDB; Data Source=” + $srv + “; Initial Catalog=master; Integrated Security=SSPI”)

                $adoConnection.Set_ConnectionTimeout($timeout)

                $adoConnection.Open()

 

                if ($adoConnection.State -eq “1”)

                {

                               Write-Debug “Connection succeeded…”

                $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic)

                $adoRecordset.MoveFirst()

                $maxMemory = ($adoRecordset.Fields.Item(“value”).Value)

                if ($maxMemory -eq 2147483647)

                {

                                               Write-Verbose “Max memory is set to unlimited”

                      $maxMemory = 1

                }

                $adoRecordset.Close()

                $adoConnection.Close()

                }

                else

                {

                               Write-Debug “Connection failed…”

                   $maxMemory = 2

                }

                Write-Debug “Max memory is set to $maxMemory”

                Write-Output $maxMemory

}

 

main

*** CREATE TABLE ***

# ======================================================

## Title       : CreateTable-MSSQL-UsingSMO

## Description : Create a new table using SMO

## Author      : Idera

## Date        : 1/28/2009

## Input       : -serverInstance <server\instance>

##                                                            -dbName <database name>

##                                                            -schemaName <schema name>

##                                                            -tblName <table name>

##                                                            -verbose

##                                                            -debug

## Output      : Create a demo database and table

## Usage                                              : PS> .\CreateTable-MSSQL-UsingSMO -serverInstance MyServer -dbName SMOTestDB

##                                                                                                                                                        -schemaName SMOSchema -tblName SMOTable -verbose -debug

## Notes                                               : Adapted from Allen White script

## Tag                                   : SMO, SQL Server, Table

## Change log  : Revised SMO Assemblies

## ======================================================

 

param

(

                [string]$serverInstance = “(local)”,

                [string]$dbName = “SMOTestDB”,

                [string]$schemaName = “SMOSchema”,

                [string]$tblName = “SMOTable”,

                [switch]$verbose,

                [switch]$debug

)

 

function main()

{

                if ($verbose) {$VerbosePreference = “Continue”}

                if ($debug) {$DebugPreference = “Continue”}

                CreateTableMSSQLUsingSMO $serverInstance $dbName $schemaName $tblName

}

 

function CreateTable-MSSQL-UsingSMO($serverInstance, $dbName, $schemaName, $tblName)

{

                trap [Exception]

                {

                               write-error $(“TRAPPED: “ + $_.Exception.Message);

                               continue;

                }

               

                # Load SMO assemblies

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Management.Common” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoEnum” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.Smo” );

                [void][reflection.assembly]::LoadWithPartialName( “Microsoft.SqlServer.SmoExtended “ );

               

                # Instantiate a server object for the default instance

                $namedInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) ($serverInstance)

               

                # Disable connection pooling

                $namedInstance.ConnectionContext.Set_NonPooledConnection($TRUE)

               

                # Explicitly connect because connection pooling is disabled

                $namedInstance.ConnectionContext.Connect()

 

                cls

               

                # Connect to the server with Windows Authentication and drop database if exist

                if ($namedInstance.Databases[$dbName] -ne $null)

                {

                               Write-Debug “The test database already exists on $namedInstance”

                               Write-Debug “Deleting it now…”

                               $namedInstance.Databases[$dbName].drop()

                }

 

                # Instantiate a database object

                Write-Debug “Creating database: $dbName”

                $database = newobject(“Microsoft.SqlServer.Management.Smo.Database”) ($namedInstance, $dbName)

               

                # Create the new database on the server

                $database.Create()

               

                # Instantiate a schema object

                Write-Debug “Creating schema: $SchemaName”

                $schema = newobject(“Microsoft.SqlServer.Management.Smo.Schema”) ($database, $schemaName)

               

                # Create the new schema on the server

                $schema.Create()

               

                # Instantiate a table object

                Write-Debug “Creating table: $TblName”

                $table = newobject(“Microsoft.SqlServer.Management.Smo.Table”) ($Database, $TblName)

               

                # Add Field1 column

                $colField1 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field1”)

                # TIP: setting an object type from a NetClassStaticMethod

                $colField1.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::Int

                $table.Columns.Add($colField1)

               

                # Add Field2 column

                $colField2 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field2”)

                $colField2.DataType =  [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)

                $table.Columns.Add($colField2)

               

                # Add Field3 column

                $colField3 = NewObject(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field3”)

                $colField3.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(50)

                $Table.Columns.Add($colField3)

               

                # Set the schema property of the table.

                $table.Schema = $schemaName

               

                # Create the table on the server

                $table.Create()

               

                Write-Host “Table: $dbName.$schemaName.$tblName created”

               

                # Explicitly disconnect because connection pooling is disabled

                Write-Debug “Disconnecting…”

                $namedInstance.ConnectionContext.Disconnect()

}

 

main

 

[EN] Do You like Idera solutions? Take one product free!

Logo_IDERA If You are MVP, and like (or want) some interesting tools for SQL Server or SharePoint Server go to the IDERA site and take one product with Your personal license for free. You can choose one from a several very nice tools:

SQL Server Products Enterprise Software:
SQL compliance manager – Capture, audit & alert on SQL Server activity
SQL defrag manager – Automate & optimize database defragmentation 
SQL diagnostic manager – SQL Server performance monitoring, alerting and diagnostics 
SQL safe backup – Accelerate, compress and encrypt database backups
SQL secure – Manage SQL Server security and permissions

Must-Have Tools for DBA’s & Developers:
SQL admin toolset – 25 essential SQL Server administration tools
SQL comparison toolset – SQL Server object and data comparison
SQL safe lite – SQL Server backup compression
SQL virtual database – Access data in SQL Server backup files without restoring

Tools Bundle:
SQL toolbox – Essential tools for DBAs and developers

SharePoint Products
SharePoint backup – Backup and Recover SharePoint
SharePoint diagnostic manager – Enterprise-class performance and availability monitoring for SharePoint 
SharePoint migration suite – Architect, organize and migrate SharePoint content
SharePoint information architect – Capture, visualize, edit and deploy SharePoint Taxonomies
SharePoint content organizer – Tag and organize your SharePoint content in bulk 
SharePoint migration manager – Migrate and organize SharePoint content
SharePoint security manager – Fast, effective SharePoint security administration
SharePoint admin toolset – 11 Tools to Simplify SharePoint Management

PowerShell Products
PowerShell Plus – Advanced PowerShell development environment

This offer is valid only for current MVP’s. Get this HERE. Really nice, isn’t?