Enable-PSRemoting -Force

$cred = Get-Credential nwtraders\administrator
Enter-PSSession -ComputerName sql1 -Credential $cred

Set-Item WSMan:\localhost\Client\TrustedHosts -Value * -Force

$cred = Get-Credential SQL2005-Host1\Administrator
Invoke-Command `
    -ComputerName SQL2005-Peer1 `
    -ScriptBlock {ping SQL2005-Peer2 -4} `
    -Credential $cred

#
#      Syntax: & GetIndexes.ps1 "SQLServer\Instance" "Database" "OutputPathAndFileName"
#
#   In order for PowerShell to run scripts, run Set-ExecutionPolicy RemoteSigned
#
#      Notes:
#      1. If the specified output file already exists, it is deleted without warning.
#      2. If the specified output path does not exist, it is created.
#---------------------------------------------------------------------------------------------------
#                                       PARAMETERS (Required)
#---------------------------------------------------------------------------------------------------
# $SQLInstance [string] - the name of the sql instance, in server\instance format.
# $Database [string]    - the name of the database to script out indexes for.
# $FileName [string]    - the location of the output file.
#---------------------------------------------------------------------------------------------------
#                                       PARAMETERS (Optional)
#---------------------------------------------------------------------------------------------------
# $Schema [string]          - the name of the schema to script out indexes for. If not specified, uses all.
# $Table [string]           - the name of the table to script out indexes for. If not specified, uses all.
# $IncludeDrop [boolean]    - set to true (default) to have the script generate drop statements and
#                             existance check prior to dropping.
# $AppendToFile [boolean]   - set to true (default) to append to the file. False will overwrite the file.
# $IncludeHeaders [boolean] - set to true (default) to include scripting comments.
# $NoFileGroup [boolean]    - set to true (default) to suppress filegroup placement options in generated script.
# $ToFileOnly [boolean]     - set to true (default) to send generated script to the file only.
#                             Setting to false will also send the generated script to the screen.
#---------------------------------------------------------------------------------------------------
#                                         MODIFICATION LOG
#---------------------------------------------------------------------------------------------------
#2012-08-31 WGS Initial Creation.
#---------------------------------------------------------------------------------------------------
 
param(
       [String] $SQLInstance,                # the SQL instance to generate a script from, in Server\Instance format.
       [String] $Database,                   # the database to generate the index scripts from.
       [String] $Filename,                   # the output file for the generated scripts.
       #Optional parameters follow
       [String] $SchemaName,                 # if specified, restricts generated scripts to tables in this schema.
       [String] $TableName,                  # if specified, restricts generated scripts to tables with this name.
       [boolean] $IncludeDrop = $True,       # set to $True to include drop statements
       [boolean] $AppendToFile = $True,      # set to $False to overwrite file
       [boolean] $IncludeHeaders = $True,    # set to $False to not have the scripting headers
       [boolean] $NoFileGroup = $True,       # set to $False to include filegroup placement in script
       [boolean] $ToFileOnly = $True         # set to $False to send results to screen also
)
cls
 
if ((!$SQLInstance) -or (!$Database) -or (!$Filename))
{
       Write-Warning 'Syntax: & "GetIndexes.ps1" "SQLServer\Instance" "DatabaseName" "Output Path and Filename" '
       Write-Warning 'Syntax (Optional Parameters): '
       Write-Warning '  "SchemaName"'
       Write-Warning '  "TableName"'
       Write-Warning '  "Include Drop Statements" (boolean)'
       Write-Warning '  "Append each object''s script to file" (boolean)'
       Write-Warning '  "Include Script Headers" (boolean)'
       Write-Warning '  "Don''t include filegroup placement settings" (boolean)'
       Write-Warning '  "Send script output to file only" (boolean)'
       Write-Warning '    "Pass in boolean values as $True or $False"'
       Write-Host ""
}
else
{
       # if the file already exists, delete it
       if (Test-Path $Filename) {Remove-Item $Filename}
       # get the path that this file is in
       $Path = Split-Path $Filename -Parent
       # if the path doesn't exist, create it
       if (!(Test-Path -path $Path)) {New-Item $Path -Type Directory}
       Write-Host "Output file: $Filename"
 
       #Load SMO, connect to server and database
       [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null 
       $server =  New-Object ('Microsoft.SqlServer.Management.SMO.Server') "$SQLInstance"
       $db = $server.Databases[$Database]
 
       # this object is used to script the actual index
       $Scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($server)
       $Scriptr.Options.ScriptDrops = $False
       $Scriptr.Options.Indexes = $True
       $Scriptr.Options.IncludeHeaders = $IncludeHeaders
       $Scriptr.Options.ClusteredIndexes = $True
       $Scriptr.Options.NonClusteredIndexes = $True
       $Scriptr.Options.XmlIndexes = $True
       $Scriptr.Options.AppendToFile = $AppendToFile
       $Scriptr.Options.FileName = $Filename
       $Scriptr.Options.ToFileOnly = $ToFileOnly
       $Scriptr.Options.NoFileGroup = $NoFileGroup
 
       foreach ($table in $db.Tables)
       {
              if (((!$TableName) -or ($TableName -eq $table.name)) -and
                     ((!$SchemaName) -or ($SchemaName -eq $table.Schema)))
              {
                     foreach ($index in $table.Indexes)
                     {
                           Write-Host "Database: $db; Table: $table; Index: $index"
 
                           if ($IncludeDrop -eq $True)
                           {
                                  $Scriptr.Options.IncludeIfNotExists = $True
                                  $Scriptr.Options.ScriptDrops = $True
                                  $Scriptr.Script($index)
                           }
                           $Scriptr.Options.IncludeIfNotExists = $False
                           $Scriptr.Options.ScriptDrops = $False
                           $Scriptr.Script($index)
                     }
              }
       }
}

notepad $ENV:TEMP\saPwdList.csv

$Items = Import-CSV $ENV:TEMP\saPwdList.csv -Delimiter ","| #Use the chosen delimiter
ForEach ($Item in $Items)
{
  $Instance = $Item.ServerName
  $Pwd      = $Item.Pwd
 
  $Server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance
  $Server.Logins.Item('sa').ChangePassword($Pwd)
  $Server.Logins.Item('sa').Alter()
}

param(
  [string]$LoginName
  )
 
$Items = Import-CSV $ENV:TEMP\ServerList.csv -Delimiter | #Use the chosen delimiter
ForEach ($Item in $Items)
{
    $Instance = $Item.ServerName
    $Pwd      = $Item.Pwd
 
    $Server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance
    $Login = $Server.Logins[$LoginName]
    if (($Login)) #Check to see if the login exists
    {
        $Login.ChangePassword($Pwd)
        $Login.Alter()
    }
}

#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
#Run the consistency check
$MyDB.CheckCatalog([Microsoft.SqlServer.Management.Smo.RepairType]::None)
$MyDB.CheckAllocations([Microsoft.SqlServer.Management.Smo.RepairType]::None)
$MyDB.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None)

#Assign variables
$Instance   = "localhost\SQL2012"
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
$Job = $Server.JobServer.Jobs["Database Consistency Checks"]
IF (!($Job))
{
    # Create the job
    $Job = New-Object ("$SMO.Agent.Job") ($Server.JobServer, 'Database Consistency Checks')
    $Job.OwnerLoginName = 'sa'
    $Job.Create()
}
 
$JobStep = $Job.JobSteps["Run DBCC on all databases"]
IF (!($JobStep))
{
    # Create the job step to run DBCC CHECKDB for all databases
    $JobStep = New-Object ("$SMO.Agent.JobStep") ($Job, "Run DBCC on all databases")
    $JobStep.Command = "EXECUTE sp_msForEachDB 'DBCC CHECKDB([?])'"
    $JobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess
    $JobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure
    $JobStep.Create()
}
 
$JobSchedule = $Job.JobSchedules["Saturday's at 3am"]
IF (!($JobSchedule))
{
    # Create the job schedule to run on Saturdays at 3am
    $JobSchedule = New-Object ("$SMO.Agent.JobSchedule") ($Job, "Saturday's at 3am")
    $JobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.Smo.Agent.FrequencyTypes]::Weekly
    $JobSchedule.FrequencyInterval = [Microsoft.SqlServer.Management.Smo.Agent.WeekDays]::Saturday
    $JobSchedule.FrequencyRecurrenceFactor = 1 # Run every 1 week
    $TimeSpanStart = New-Object -TypeName TimeSpan -ArgumentList 3,0,0
    $JobSchedule.ActiveStartTimeOfDay = $TimeSpanStart
    $JobSchedule.Create()
}

Get-ChildItem SQLSERVER:SQL\localhost\SQL2012\JobServer

Get-ChildItem SQLSERVER:SQL\localhost\SQL2012\JobServer\Alerts | `
    Select-Object Name, MessageID | Where-Object MessageID -in 823,824,825,829 | `
    Format-Table –Autosize

#Assign variables
$Instance   = "localhost\SQL2012"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
# Get the Job Server
$JobServer = $Server.JobServer
 
# First, ensure that a local operator exists.
$Operator = $JobServer.Operators["LocalHost"]
IF (!($Operator)) #Check if the operator exists
{
    # Operator does not exist, so create it
    $Operator = New-Object ("$SMO.Agent.Operator") ($JobServer, "LocalHost")
    $Operator.NetSendAddress = "LocalHost"
    $Operator.Create()
}
 
ForEach ($MessageID in (823,824,825,829))
{
    $AlertName = "Alert: Message Id: $MessageID"
    # Get the alert if it already exists
    # You could search for the alert by name, however you can only have one alert per messageid.
    # So, search by the message id.
    $Alert = $JobServer.Alerts | Where-Object Severity -EQ $Severity
 
    IF (!($Alert)) #Check to see if the alert already exists
    {
        # The alert does not exist, so add it
        $Alert = New-Object ("$SMO.Agent.Alert") ($JobServer, $AlertName)
        $Alert.MessageID = $MessageID
        $Alert.Create()
 
        # Add the operator to the alert
        $Alert.AddNotification("LocalHost", [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NetSend)
    }
}

#Assign variables
$Instance   = "localhost\SQL2012"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
# Get the Job Server
$JobServer = $Server.JobServer
 
#First, ensure that a local operator exists.
$Operator = $JobServer.Operators["LocalHost"]
IF (!($Operator)) #Check if the operator exists
{
    #Operator does not exist, so create it
    $Operator = New-Object ("$SMO.Agent.Operator") ($JobServer, "LocalHost")
    $Operator.NetSendAddress = "LocalHost"
    $Operator.Create()
}
 
ForEach ($Severity in (22,23))
{
    $AlertName = "Alert: Severity: $Severity"
    # Get the alert if it already exists
    # You could search for the alert by name, however you can only have one alert per Severity.
    # So, search by the Severity.
    $Alert = $JobServer.Alerts | Where-Object Severity -EQ $Severity
 
    IF (!($Alert)) #Check to see if the alert already exists
    {
        # The alert does not exist, so add it
        $Alert = New-Object ("$SMO.Agent.Alert") ($JobServer, $AlertName)
        $Alert.Severity = $Severity
        $Alert.Create()
 
        #Add the operator to the alert
        $Alert.AddNotification("LocalHost", [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NetSend)
    }
}

#clear variables
$Server     = $null
 
#Assign variables
$Instance   = "localhost\SQL2008"
$VerifyDB   = 'Verify'
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# Restoring to a new database changes the $Server.Databases collection, raising an error.
# So, create an empty array to hold the current list of databases and loop through that.
$Databases = @()
# Add each database to this array
ForEach ($db in $Server.Databases)
{
    IF ($db.Status -eq 'Normal' -and $db.IsMirroringEnabled -eq $false -and `
        $db.Name -ne 'tempdb' -and $db.Name -ne $VerifyDB)
    {
        $Databases = $Databases + $db
    }
}
 
ForEach ($db in $Databases)
{
    # Get the location to store the data files at for this verify database
    $Verify = $Server.Settings.DefaultFile
    IF (!($Verify.EndsWith('\'))) {$Verify = $Verify + '\'}
    $Verify = $Verify + "$($VerifyDB)_DBFiles\"
 
    # Ensure that the directory exists
    IF (!(Test-Path -Path $Verify)) {New-Item $Verify -Type Directory}
 
    $dir = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Full\"
    # Get the most recent full backup
    $Full = $Null
    IF (Test-Path -Path $dir)
    {
        $Full = Get-ChildItem $dir |`
            Sort-Object LastWriteTime -Descending |`
            Select-Object -First 1 |`
            Select-Object Name, FullName, LastWriteTime
    }
 
    IF ($Full)
    {
        # Restore the full backup
        $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Full.FullName, 'File')
        $Restore = New-Object ("$SMO.Restore")
        $Restore.Checksum = $True
        $Restore.Devices.Add($BackupDevice)
        $Restore.Database = "$VerifyDB"
        $Restore.NoRecovery = $True
        $Restore.ReplaceDatabase = $True
 
        #Relocate the files so that they don't stomp on the existing db files
        ForEach ($FileGroup in $DB.FileGroups)
        {
            ForEach ($File in $FileGroup.Files)
            {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
            }
        }
 
        ForEach ($File in $DB.LogFiles)
        {
            $FileName = Split-Path $File.FileName -Leaf #Get just the filename
            $FileName = $Verify + $FileName
            $NewFileLoc = New-Object ("$SMO.RelocateFile") ($File.Name, $FileName)
            $Restore.RelocateFiles.Add($NewFileLoc) | Out-Null
        }
        $RelocateFiles = $Restore.RelocateFiles
        Write-Host "Restoring Full backup file $($Full.FullName) to $VerifyDB database"
        $Restore.SqlRestore($Server)
 
        $LastWriteTime = $Full.LastWriteTime
 
        # Get the most recent differential backup
        $Diff = $null
        $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Diff\"
        IF (Test-Path -Path $TestPath)
        {
            $Diff = Get-ChildItem $TestPath |`
                Sort-Object LastWriteTime -Descending |`
                Select-Object -First 1 |`
                Select-Object Name, FullName, LastWriteTime
 
            # if the latest diff was before the latest full, don't do anything with it.
            IF ($Diff.LastWriteTime -LT $Full.LastWriteTime) {$Diff = $null}
            ELSE {$LastWriteTime = $Diff.LastWriteTime}
        }
 
        # If there is one,
        IF ($Diff)
        {
            $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($Diff.FullName, 'File')
            $Restore = New-Object ("$SMO.Restore")
            $Restore.Checksum = $True
            $Restore.Database = "$VerifyDB"
            $Restore.Devices.Add($BackupDevice)
            $Restore.NoRecovery = $True
            ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
            Write-Host "Restoring Differential backup file $($Diff.FullName) to $VerifyDB database"
            $Restore.SqlRestore($Server)
        }
 
        IF ($DB.RecoveryModel -ne "Simple")
        {
            # Restore all transaction logs written since the last full/diff backup
            $TestPath = $Server.Settings.BackupDirectory + "\" + $db.Name + "\Log\"
            IF (Test-Path -Path $TestPath)
            {
                ForEach ($File in Get-ChildItem $TestPath |`
                            Where {$_.LastWriteTime -ge $LastWriteTime} |`
                            Sort-Object LastWriteTime |`
                            Select-Object Name, FullName )
                {
                    $BackupDevice = New-Object ("$SMO.BackupDeviceItem") ($File.FullName, 'File')
                    $Restore = New-Object ("$SMO.Restore")
                    $Restore.Checksum = $True
                    $Restore.Database = "$VerifyDB"
                    $Restore.Devices.Add($BackupDevice)
                    $Restore.NoRecovery = $True
                    ForEach ($RelocateFile in $RelocateFiles) {$Restore.RelocateFiles.Add($RelocateFile) | Out-Null}
                    Write-Host "Restoring Transaction Log backup file $($File.FullName) to $VerifyDB database"
                    $Restore.SqlRestore($Server)
                }
            }
        }
        # Bring the database online
        $Restore.NoRecovery = $False
        Write-Host "Bringing $VerifyDB database online"
        $Restore.SqlRestore($Server)
        Write-Host "Test Restore of the [$($db.name)] database passed"
 
        # This would be a good time to do a checkdb!
    }
}