Enhancing Your Troubleshooting Toolset with PowerShell - SQL Server 2012 Internals and Troubleshooting - page 403

$ViewTriggersBackupFolder = Join-Path -Path
$ViewsBackupFolder -ChildPath "Triggers"
#Loop through the views to script them out
foreach ($view in $database.Views | where
{$_.IsSystemObject -eq $false})
{
#Script the view
Write-DDLOutput (Join-Path -Path
($ViewsBackupFolder) -ChildPath
($view.Schema + "." + $view.Name + ".sql")) $view
foreach ($index in $view.Indexes)
{
#Generate a filename for the table
switch($index.IndexKeyType)
{
DriPrimaryKey
{
$filename = Join-Path -Path
($ViewKeysBackupFolder) -ChildPath
($view.Schema + "." +
$view.Name + "." + $index.Name + ".sql")
}
default
{
$filename = Join-Path -Path
($ViewIndexesBackupFolder) -ChildPath
($view.Schema + "." + $view.Name + "." +
$index.Name + ".sql")
}
}
Write-DDLOutput $filename $index
}
foreach ($trigger in $view.Triggers)
{
#Script the trigger
Write-DDLOutput (Join-Path -Path
($ViewTriggersBackupFolder) -ChildPath
($view.Schema + "." + $view.Name + "." +
$trigger.Name + ".sql")) $trigger
}
}
}
Scheduling Script Execution
There are two methods for scheduling script execution. The i rst is to use Windows Task Scheduler,
which is useful if you don't have SQL Server installed on the server from which you wish to execute
the PowerShell script. You can simply add a new task to the Scheduler and execute PowerShell.exe,
passing the script you want to execute as a parameter.