I like to script the Indexes manually to apply to a subscriber when creating a publication. Call me a sadist, but I am what I am. I whipped up a quick script to connect to a published database, grab the articles in the publication and script out the indexes on said articles (obviously, not the clustered index). It’s very very slow, but I’m too tired to try to optimize this now. Use at your own risk. Minimally tested and not a lot of error handling is involved.
param(
[string]$InstanceName = {throw "You must enter an Instance Name."},
[string]$PubName = {throw "You must enter the name of the publication for which to script the indexes."},
[string]$DistDBName = {throw "You must enter the name of the published database."},
[string]$FileLocation = {throw "You must enter the location of where you want the scripts to be put."}
)
function ScriptIndexes([string]$TableName)
{
$Table = Get-ChildItem | where {$_.Name -eq $TableName}
foreach($ix in $Table.Indexes)
{
if($ix.IsClustered -eq $false)
{
$ix.Script() | out-file "$FileLocation\$($Table.Name).sql" -append; "GO`n$($separator)`n" | out-file "$FileLocation\$($Table.Name).sql" -append -Force
}
}
}
function Main()
{
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$smo = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($smo.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9')
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}
# Handle any errors that occur
Trap
{
# Handle the error
$err = $_.Exception
write-host $err.Message
while( $err.InnerException )
{
$err = $err.InnerException
write-output $err.Message
};
# End the script.
break
}
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
$DistQuery = @"
SELECT art.name AS TableName FROM dbo.sysarticles art (NOLOCK)
INNER JOIN dbo.syspublications pub
ON art.pubid = pub.pubid
WHERE pub.name = '$PubName'
"@
$PubRes = invoke-sqlcmd -ServerInstance $InstanceName -Database $DistDBName -Query $DistQuery
sl SQLSERVER:SQL\$InstanceName\DATABASES\$DistDBName\TABLES
foreach($row in $PubRes)
{
ScriptIndexes $row.TableName
}
}
cls
Main
Sql Server Powershell 