It 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”}
CheckDB–MSSQL–UsingADO $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”}
Get–MSSQL–MaxMemory $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”}
CreateTable–MSSQL–UsingSMO $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 = new–object(“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 = new–object(“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 = new–object(“Microsoft.SqlServer.Management.Smo.Table”) ($Database, $TblName)
# Add Field1 column
$colField1 = New–Object(“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 = New–Object(“Microsoft.SqlServer.Management.Smo.Column”) ($table, “Field2”)
$colField2.DataType = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarchar(25)
$table.Columns.Add($colField2)
# Add Field3 column
$colField3 = New–Object(“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