PowerShell Snippets A Great Learning Tool

When I talk to people about Powershell they often ask how can they easily learn the syntax. Here’s a good tip

Open PowerShell ISE and press CTRL + J

image

You will find a number of snippets that will enable you to write your scripts easily.  Johnathan Medd PowerShell MVP has written a good post about snippets on the Hey, Scripting Guy! blog so I will not repeat that but suggest that you go and read that post. It will show you how quickly and easily you will be able to write more complex Powershell scripts as you do not have to learn the syntax but can use the snippets to insert all the code samples you require.

Not only are there default snippets for you to use but you can create your own snippets. However there isn’t a snippet for creating a new snippet so here is the code to do that

 $snippet1 = @{
 Title = 'New-Snippet'
 Description = 'Create a New Snippet'
 Text = @"
`$snippet = @{
 Title = `'Put Title Here`'
 Description = `'Description Here`'
 Text = @`"
 Code in Here 
`"@
}
New-IseSnippet @snippet
"@
}
New-IseSnippet @snippet1 –Force

I frequently use the SQL Server SMO Object in my code so I created this snippet

$snippet = @{
 Title = 'SMO-Server'
 Description = 'Creates a SQL Server SMO Object'
 Text = @"
 `$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$Server
"@
}
New-IseSnippet @snippet

I also use Data Tables a lot so I created a snippet for that too

$snippet = @{
 Title = 'New-DataTable'
 Description = 'Creates a Data Table Object'
 Text = @"
 # Create Table Object
 `$table = New-Object system.Data.DataTable `$TableName
 
 # Create Columns
 `$col1 = New-Object system.Data.DataColumn NAME1,([string])
 `$col2 = New-Object system.Data.DataColumn NAME2,([decimal])
 
 #Add the Columns to the table
 `$table.columns.add(`$col1)
 `$table.columns.add(`$col2)
 
 # Create a new Row
 `$row = `$table.NewRow() 
 
 # Add values to new row
 `$row.Name1 = 'VALUE'
 `$row.NAME2 = 'VALUE'
 
 #Add new row to table
 `$table.Rows.Add($row)
"@
 }
 New-IseSnippet @snippet

Denniver Reining has created a Snippet Manager which you can use to further expand your snippets usage and it is free as well.

If you have further examples of useful snippets please feel free to post them in the comments below

Edit 16/12/2014

I am proud that this article was nominated for the Tribal Awards. Please go and vote for your winners in all the categories

http://www.sqlservercentral.com/articles/Awards/119953/

Personally in the article category I will be voting for

Gail Shaw’s SQL Server Howlers

Find Out Which Indexes are on which Filegroups using PowerShell And How To Find Other Information

 

A short post today to pass on a script I wrote to fulfil a requirement I had.

Which indexes are on which filegroups. I found a blog post showing how to do it with T-SQL but as is my wont I decided to see how easy it would be with PowerShell. I also thought that it would make a good post to show how I approach this sort of challenge.

I generally start by creating a SQL Server SMO Object You can use the SMO Object Model Diagram or Get-Member to work out what you need. As we are talking indexes and filegroups I will also create a Database object

 
$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]

Then by piping the database object to Get-Member I can see the properties

image

Lets take a look at the table object in the same way

image

 

I can see the indexes object so I pipe that to Get-Member as well

image

Now I have enough to information to create the report. I will select the Name, Table, Type and Space Used of the Indexes and format them nicely

$Server = "SQL2012Ser2012"
$DBName = "AdventureWorks2012"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DB = $srv.Databases[$DBName]
$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|Format-Table –AutoSize

and here are the results

image

However, you may want the results to be displayed in a different manner, maybe CSV,HTML or text file and you can do this as follows

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Csv c:\temp\filegroups.csv
Invoke-Item c:\temp\filegroups.csv

image

 

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed| Out-File c:\temp\filegroups.txt
Invoke-Item c:\temp\filegroups.txt

image

$db.tables.Indexes|select Name,Parent,Filegroup,IndexType,SpaceUsed|ConvertTo-Html |Out-File c:\temp\filegroups.html
Invoke-Item c:\temp\filegroups.html

image

Hopefully this has shown you how easy it can be to use PowerShell to get all of the information that you need from your SQL Server and how to approach getting that information as well as several ways to display it

Refreshing Availability Group Database with PowerShell

Following last weeks post on Refreshing A Mirrored Database with PowerShell I thought I would write the script to refresh an Availability Group Database.

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases.You can read more about Availability groups here

There are situations where you may need to refresh these databases. Disaster Recovery is an obvious one but also during development to provide testing or development environments to test your High Availability implementations, run through disaster scenarios, create run books or ensure that the code changes still work with AG. There are other scenarios but this post covers the automation of restoring an Availability Group Database from a backup.

The steps that you need to take to restore an Availability Group Database are

Remove Database from the Availability Group
Restore the Primary Replica Database
Backup the Primary Replica Database Transaction Log
Restore the Secondary and Tertiary Replica Databases with no recovery
Add the Database back into the Availability Group
Resolve Orphaned Users – Not covered in this script
Check the status

Here is my set up for this post

image

I have 3 servers SQL2012SER08AG1, SQL2012SER08AG2 and SQL2012SER08AG3 with 3 databases in an Availability Group called AG_THEBEARD1. SQL2012SER08AG2 is set up as a secondary replica using Synchronous-Commit Mode SQL2012SER08AG3 is set up as a read only replica using Asynchronous-Commit Mode. I have three databases in my Availability Group and today I shall use the database called TestDatabase (I have no imagination today!) to demonstrate the refresh

The script requires some variables to be set up at the beginning. You can easily change this and make the script into a function and call it if you desire, but for this post I shall consider the script as a standalone. The reasoning for this is that I imagine that it will be placed into a run book or stored for use in a repository for specific use and therefore reduces any pre-requisites for using it.

First we will remove the database from the Availability Group. This is achieved using the Remove-SqlAvailabilityDatabase CMDLet

 
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName 
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

 

Next  Restore the Primary Replica Database, Backup the Primary Replica Database Transaction Log
and Restore the Secondary and Tertiary Replica Databases with no recovery using Restore-SqlDatabase and Backup-SqlDatabase (You can also use the SMO method in the previous post if you wish)

 

 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $PrimaryServer -ReplaceDatabase

# Backup Primary Database
Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log'


# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer
$srv.KillAllProcesses($dbname)

# Restore Secondary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $SecondaryServer -NoRecovery -ReplaceDatabase 
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer
$srv.KillAllProcesses($dbname)

# Restore Tertiary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $TertiaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $TertiaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Then add the database back to the Availability Group

 
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName 

Finally test the status of the Availability Group

 
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
   $AG = $srv.AvailabilityGroups[$AGName]
   $AG.DatabaseReplicaStates|ft -AutoSize

I also like to add some output to show the progress of the script. This can be logged using Out-File or displayed on the screen using Out-Host.

 
$EndDate = Get-Date
$Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script anded at $EndDate and took
$Time
" -ForegroundColor Green

Here are the results of my script

image

Here is the script

<#

    .NOTES 
    Name: Availability Group Refresh
    Author: Rob Sewell http://sqldbawithabeard.com
    
    .DESCRIPTION 
        Refreshes an Availbaility group database from a backup

        YOU WILL NEED TO RESOLVE ORPHANED USERS IF REQUIRED
#> 

## http://msdn.microsoft.com/en-gb/library/hh213078.aspx#PowerShellProcedure
# http://msdn.microsoft.com/en-us/library/hh213326(v=sql.110).aspx
cls

# To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’)  | out-null

$LoadServer = "SQL2012Ser2012" # The Load Server 

$Date = Get-Date -Format ddMMyy
$PrimaryServer = "SQL2012SER08AG1" # The Primary Availability Group Server
$SecondaryServer = "SQL2012SER08AG2" # The Secondary Availability Group Server
$TertiaryServer = "SQL2012SER08AG3" # The Tertiary Availability Group Server
$AGName = "AG_THEBEARD1" # Availability Group Name
$DBName = "TestDatabase" # Database Name

$LoadDatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\LoadTestDatabase" + $Date + ".bak" # Load database Backup location - Needs access permissions granted
$DatabaseBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\TestDatabase" + $Date + ".bak" # database Backup location - Needs access permissions granted
$LogBackupFile = "\\sql2012ser2012\Backups\GoldenBackup\TestDatabase" + $Date + ".trn" # database Backup location - Needs access permissions granted

# Path to Availability Database Objects
$MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgSecondaryPath = "SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName"
$MyAgTertiaryPath = "SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName"

$StartDate = Get-Date
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script Started $StartDate

" -ForegroundColor Green


cd c:

# Remove old backups
If(Test-Path $LoadDatabaseBackupFile){Remove-Item -Path $LoadDatabaseBackupFile -Force}
If(Test-Path $DatabaseBackupFile){Remove-Item -Path $DatabaseBackupFile}
If(Test-Path $LogBackupFile ) {Remove-Item -Path $LogBackupFile }

Write-Host "Backup Files removed" -ForegroundColor Green

# Remove Secondary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$SecondaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$SecondaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName 

Write-Host "Secondary Removed from Availability Group" -ForegroundColor Green

# Remove Tertiary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$TertiaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$TertiaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

Write-Host "Tertiary removed from Availability Group" -ForegroundColor Green


# Remove Primary Replica Database from Availability Group to enable restore
cd SQLSERVER:\SQL\$PrimaryServer\DEFAULT
Remove-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\$PrimaryServer\DEFAULT\AvailabilityGroups\$AGName\AvailabilityDatabases\$DBName

Write-Host "Primary removed from Availability Group" -ForegroundColor Green

# Backup Load Database
Backup-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile -ServerInstance $LoadServer

Write-Host "Load Database Backed up" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
$srv.KillAllProcesses($dbname)

# Restore Primary Replica Database from Load Database
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $PrimaryServer -ReplaceDatabase

Write-Host "Primary Database Restored" -ForegroundColor Green

# Backup Primary Database
# Backup-SqlDatabase -Database $DBName -BackupFile $DatabaseBackupFile -ServerInstance $PrimaryServer
Backup-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $PrimaryServer -BackupAction 'Log'


Write-Host "Primary Database Backed Up" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SecondaryServer
$srv.KillAllProcesses($dbname)

# Restore Secondary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $SecondaryServer -NoRecovery -ReplaceDatabase 
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $SecondaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Write-Host "Secondary Database Restored" -ForegroundColor Green

# Remove connections to database for Restore
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $TertiaryServer
$srv.KillAllProcesses($dbname)

# Restore Tertiary Replica Database 
Restore-SqlDatabase -Database $DBName -BackupFile $LoadDatabaseBackupFile  -ServerInstance $TertiaryServer -NoRecovery -ReplaceDatabase
Restore-SqlDatabase -Database $DBName -BackupFile $LogBackupFile -ServerInstance $TertiaryServer -RestoreAction 'Log' -NoRecovery  -ReplaceDatabase

Write-Host "Tertiary Database Restored" -ForegroundColor Green

# Add database back into Availability Group
cd SQLSERVER:\SQL\$PrimaryServer
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DBName 
Add-SqlAvailabilityDatabase -Path $MyAgTertiaryPath -Database $DBName 

Write-Host "Database Added to Availability Group " -ForegroundColor Green

# Check Availability Group Status
 $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $PrimaryServer
    $AG = $srv.AvailabilityGroups[$AGName]
    $AG.DatabaseReplicaStates|ft -AutoSize

    $EndDate = Get-Date
    $Time = $EndDate - $StartDate
Write-Host "
##########################################
Results of Script to refresh $DBName on
$PrimaryServer , $SecondaryServer , $TertiaryServer
on AG $AGName
Time Script ended at $EndDate and took
$Time

" -ForegroundColor Green