Some similar powershell scripts I cobbled together.

Script jobs and some other server objects I'm interested in:

Note that there should be as many "paths" as there are "output paths" if you add types to this.

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")

$Paths = "SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Jobs","SQLSERVER:\SQL\localhost\DEFAULT\Logins","SQLSERVER:\SQL\localhost\DEFAULT\LinkedServers","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Accounts","SQLSERVER:\SQL\localhost\DEFAULT\Mail\Profiles","SQLSERVER:\SQL\localhost\DEFAULT\JobServer\Operators"

$OutputPaths = "Server\Jobs","Server\Logins","Server\LinkedServers","Server\Mail\Accounts","Server\Mail\Profiles","Server\Operators"

$i = 0

do

{

$path = $Paths[$i]

$Outputpath = "Source\SQL\"+$OutputPaths[$i]

new-item -Force -path D:\ -name $Outputpath -type directory

$dirname = "D:\" + $Outputpath

cd $path

foreach ($item in Get-Childitem)

{

if ($item.Name.Substring(0,2) -eq "##")

{

echo $item.Name

continue

}

$Scripter.Options.AnsiPadding=$False

$Scripter.Options.AnsiFile=$True

$Scripter.Options.AppendToFile=$False

$Scripter.Options.Bindings=$False

$Scripter.Options.DriAll=$True

$Scripter.Options.ExtendedProperties=$False

$Scripter.Options.IncludeHeaders=$False

$Scripter.Options.IncludeIfNotExists=$False

$Scripter.Options.IncludeDatabaseRoleMemberships=$True

$Scripter.Options.IncludeDatabaseContext=$True

$Scripter.Options.Permissions=$True

$Scripter.Options.SchemaQualify=$True

$Scripter.Options.ScriptData=$False

$Scripter.Options.ScriptDrops=$False

$Scripter.Options.Statistics=$False

$Scripter.Options.ToFileOnly=$True

$p = "D:\"+$Outputpath+"\"+$item.Name.Replace("\","_").Replace(" ","_")+".sql"

$Scripter.Options.FileName=$p

$Scripter.Script($Item)

}

$i++

}

while ($i -lt $Paths.Length)

Script database objects I'm interested in (including certain specific table bits like indexes and foreign keys)

Param

(

$Database

)

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")

$TableSubtypes = "Indexes","Triggers","ForeignKeys"

$ExcludedScriptTypes = "ApplicationRoles","AsymmetricKeys","Certificates","DatabaseAuditSpecifications","Defaults","ExtendedProperties","ExtendedStoredProcedures","FileGroups","LogFiles","SymmetricKeys","ServiceBroker"

$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database

cd $Path

foreach ($objectType in Get-ChildItem)

{

if ($ExcludedScriptTypes -contains $objectType)

{

continue

}

$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases\"+$Database+"\"+$objectType

cd $Path

$dirname = "Source\SQL\Databases\" + $Database + "\" + $objectType

new-item -Force -path D:\ -name $dirname -type directory

foreach ($Item in Get-ChildItem)

{

$Scripter.Options.AnsiPadding=$False

$Scripter.Options.AnsiFile=$True

$Scripter.Options.AppendToFile=$False

$Scripter.Options.Bindings=$False

$Scripter.Options.DriAll=$True

$Scripter.Options.ExtendedProperties=$False

$Scripter.Options.IncludeHeaders=$False

$Scripter.Options.IncludeIfNotExists=$False

$Scripter.Options.IncludeDatabaseRoleMemberships=$True

$Scripter.Options.IncludeDatabaseContext=$False

$Scripter.Options.Permissions=$True

$Scripter.Options.SchemaQualify=$True

$Scripter.Options.ScriptData=$False

$Scripter.Options.ScriptDrops=$False

$Scripter.Options.Statistics=$False

$Scripter.Options.ToFileOnly=$True

$modifier = ""

if ($Item.Schema -ne $null)

{

$modifier += $Item.Schema + "."

}

if ($Item.Name -ne $null)

{

$p = $Item.Name

$modifier += $Item.Name.Replace("\","_") + "."

}

$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$Database+"\"+$objectType+"\" + $modifier + "sql"

$p = "D:\Source\SQL\Databases\"+$database+"\"+$objectType+"\" + $modifier + "sql"

echo $p

$Scripter.Script($Item)

if ($objectType -eq "Tables")

{

foreach ($subtype in $TableSubtypes)

{

foreach ($subitem in $Item.$subtype)

{

$Scripter.Options.AnsiPadding=$False

$Scripter.Options.AnsiFile=$True

$Scripter.Options.AppendToFile=$False

$Scripter.Options.Bindings=$False

$Scripter.Options.DriAll=$False

$Scripter.Options.ExtendedProperties=$False

$Scripter.Options.IncludeHeaders=$False

$Scripter.Options.IncludeIfNotExists=$False

$Scripter.Options.Permissions=$True

$Scripter.Options.SchemaQualify=$True

$Scripter.Options.ScriptData=$False

$Scripter.Options.ScriptDrops=$False

$Scripter.Options.Statistics=$False

$Scripter.Options.ToFileOnly=$True

$modifier = ""

if ($Item.Schema -ne $null)

{

$modifier += $Item.Schema + "."

}

if ($Item.Name -ne $null)

{

$modifier += $Item.Name.Replace("\","_") + "."

}

if ($subitem.Name -ne $null)

{

$modifier += $subitem.Name.Replace("\","_") + "."

}

if ($subtype -eq "Indexes")

{

if ($subitem.IsClustered)

{

$modifier += "Clustered."

}

if ($subitem.IndexKeyType -eq "DriPrimaryKey")

{

#Primary keys fall under indexes as well as primary key branches

$subtype = "PrimaryKeys"

} else

{

$subtype = "Indexes"

}

}

$dirname = "Source\SQL\Databases\" + $Database + "\" + $subtype

new-item -Force -path D:\ -name $dirname -type directory

$Scripter.Options.FileName="D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"

$p = "D:\Source\SQL\Databases\"+$database+"\"+$subtype+"\" + $modifier + "sql"

echo $p

$Scripter.Script($subitem)

}

}

}

}

}

A sort of wrapper script to iterate through all the databases I want to script like this (e.g. nightly from a job), assuming of course you called the script above "ScriptDatabase.ps1" and stored in path shown :). This could be rolled into the above script, but wanted to retain ability to take a snapshot of a single database scripts.

$Path = "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

cd $Path

foreach ($item in Get-Childitem)

{

echo $item.Name

D:\Scripts\ScriptDatabase.ps1 $item.Name

}

All of these scripts write to specific folders on the D:\ of my servers but could be adapted.

Lookup $Scripter.Options on the net for parameters to pretty much mirror anything you can do via "Generate scripts" in SSMS

Obviously a disclaimer applies: This does not cover every aspect of every type of object in SQL Server and should not be relied on in lieu of a proper backup. Adjust this to ensure that permissions / extended properties / other object types that this script excludes are included appropriately.

Regards,

Mike