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


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.

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
 # Create a new Row
 $row = $table.NewRow() 
 # Add values to new row
 $row.Name1 = 'VALUE'
 $row.NAME2 = 'VALUE'
 #Add new row to table
 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

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


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



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


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


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



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


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


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


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

# 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

# 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
" -ForegroundColor Green

Here are the results of my script


You can get the script here

Refreshing A SQL Mirrored Database Using Powershell


SQL mirroring is a means of providing high availability for your SQL database. It is available in Standard Edition and although the feature is deprecated it is still widely utilised. You can read more about it on MSDN here and Jes Borland wrote a useful post answering many questions 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 mirroring. There are other scenarios but this post covers the automation of restoring a mirrored database from a backup.

I have mentioned before and no doubt I shall again, John Sansom wrote a great post about automation and I am a strong follower of that principle.

To refresh a SQL mirror the following steps are required, there are some gotchas that you need to be aware of which I will discuss later

remove mirroring
restore principle database from backup
perform a transaction log backup of the principle database
restore both backups on the mirror server with no recovery
recreate mirroring
resolve orphaned users
check mirroring status

Regular blog followers will know that I prefer to use Powershell when I can (and where it is relevant to do so) and so I have used Powershell to automate all of the steps above

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.

Set variables as follows, the last three variables set the types for the backup action type and device type and do not need to be altered.

# Set up some variables

"$PrincipalServer = '' # Enter Principal Server Name
$MirrorServer = '' # Enter Mirror Server Name
$DBName = '' # Enter Database Name
$FileShare = '' # Enter FileShare with trailing slash
$LocationReplace = $FileShare + $DBName + 'Refresh.bak'
$LocationTran = $FileShare + $DBName + 'formirroring.trn'

$PrincipalEndPoint = 'TCP://SERVERNAME:5022' # Change as required
$MirrorEndpoint = 'TCP://SERVERNAME:5022' # Change as required
$WitnessEndpoint = 'TCP://SERVERNAME:5022' # Change as required

$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File</PRE></PRE>


After some error checking the first thing is to create server and database SMO objects

"# Create Server objects
$Principal = New-Object Microsoft.SQLServer.Management.SMO.Server $PrincipalServer
$Mirror = New-Object Microsoft.SQLServer.Management.Smo.server $MirrorServer

#Create Database Objects
$DatabaseMirror = $Mirror.Databases[$DBName]
$DatabasePrincipal = $Principal.Databases[$DBName]</PRE></PRE>

(Added Extra – Use New-ISESnippet to create a SMO Server Snippet and use CTRL + J to find it

"New-IseSnippet -Title SMO-Server -Description "Create A SQL Server SMO Object"
-Text "`$srv = New-Object Microsoft.SqlServer.Management.Smo.Server `$server"


Remove Mirroring

Before we can restore the database we need to remove mirroring


restore principle database from backup

Once mirroring has been removed we can restore the database. Stuart Moore’s Great Series provides all the code you need to backup and restore databases with Powershell. There is however a bug which can catch you out. Here’s the code

"$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
#Perform Restore

The bug is as follows, if your restore is going to take longer than 10 minutes and you are using an earlier version of SQL than SQL 2012 SP1 CU8 then you will find that the restore fails after 10 minutes. This is the default timeout. You may try to set the


Value to a larger value or 0 and this will work after SQL 2012 SP1 CU8 but prior to that you will still face the same error. The simple workaround is to use Invoke-SQLCmd2 and to script the restore as follows

"#Set up Restore using refresh backup

$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
#Perform Restore
$restore.sqlrestore($PrincipalServer) # if query time < 600 seconds
# $query = $restore.Script($PrincipalServer) # if using Invoke-SQLCMD2

perform a transaction backup of the principle database

We need to have a full and transaction log backup to set up mirroring. Again you may need to use the script method if your backup will take longer than 600 seconds.

#Setup Trans Backup
$Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup|Out-Null
$Full = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Database
$Tran = [Microsoft.SQLServer.Management.SMO.BackupActionType]::Log
$File = [Microsoft.SqlServer.Management.Smo.DeviceType]::File
$Backup.Action = $Tran
$Backup.BackupSetDescription = “Log Backup of “ + $DBName
$Backup.Database = $DBName
$BackupDevice = New-Object –TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
# Perform Backup
# $query = $Backup.Script($PrincipalServer) # if query time < 600 seconds

# Invoke-Sqlcmd2 –ServerInstance $PrincipalServer –Database master –Query $query –ConnectionTimeout 0 # comment out if not used

Restore both backups on the mirror server with no recovery

To complete the mirroring set up we need to restore the backups onto the mirror server with no recovery as follows

"#Set up Restore of Full Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServe r.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationReplace,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true
$restore.sqlrestore($MirrorServer) # if query time < 600 seconds
# $query = $restore.Script($MirrorServer) # if using Invoke-SQLCMD2

# Invoke-Sqlcmd2 -ServerInstance $MirrorServer -Database master -Query $query -ConnectionTimeout 0 # comment out if not used

# Set up Restore of Log Backup on Mirror Server
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore|Out-Null
$restoredevice = New-Object -TypeName Microsoft.SQLServer.Management.Smo.BackupDeviceItem($LocationTran,$File)|Out-Null
$restore.Database = $DBName
$restore.ReplaceDatabase = $True
$restore.NoRecovery = $true

Recreate mirroring

You recreate mirroring in the same way as you would if you were using T-SQL simply add the principal endpoint to the mirror, and the mirror and witness endpoints to the principal

"#Recreate Mirroring
$DatabaseMirror.MirroringPartner = $PrincipalEndPoint
$DatabasePrincipal.MirroringPartner = $MirrorEndpoint
$DatabasePrincipal.MirroringWitness = $WitnessEndpoint

Resolve orphaned users

You will need to resolve any users and permissions on your destination servers. I do not know a way to do this with PowerShell and would be interested if anyone has found a way to replace the password or the SID on a user object, please contact me if you know.

Many people do this with the sp_rev_logins stored procedure which will create the T-SQL for recreating the logins. However, Powershell cannot read the outputs of the message window where the script prints the script. If you know that your logins are staying static then run sp_rev_logins and store the output in a sql file and call it with Invoke-SQLCmd2

"$SQL = ‘’ #Path to File
Invoke-Sqlcmd2 –ServerInstance $Server –Database master –InputFile $SQL

The other option is to set up a SSIS package following this blog post and call it from Powershell as follows

"Invoke-Command –ComputerName $Server –scriptblock {DTExec.exe /File “PATHTOPackage.dtsx”} 

This requires Powershell Remoting to have been set up on the server which may or may not be available to you in your environment.

IMPORTANT NOTE – The script does not include any methods for resolving orphaned users so you will need to test and then add your own solution to the script.

check mirroring status

Lastly you want to check that the script has run successfully and that mirroring is synchronised (I am from the UK!!) To do this I check that time and file used for the last database backup using this script

"#Check that correct file and backup date used

$query = "SELECT TOP 1 [rs].[destination_database_name] as 'database',
[rs].[restore_date] as 'restoredate',
[bs].[backup_finish_date] as 'backuptime',
[bmf].[physical_device_name] as 'Filename'
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC"

Invoke-Sqlcmd2 -ServerInstance $PrincipalServer -Database msdb -Query $query |Format-Table -AutoSize –Wrap

and that mirroring has synchronised using the following Powershell command

"$DatabasePrincipal | select Name, MirroringStatus, IsAccessible |Format-Table -AutoSize

Depending on your needs you may add some error checking using the results of the above scripts. As I said at the top of the post, you can turn this script into a function and call it at will or add it to an Agent Job for regular scheduling or just kept in a folder ready to be run when required. The choice is yours but all usual rules apply. Don’t believe anything you read on this blog post, don’t run any scripts on production, test before running any scripts, understand what the code is doing before you run it or I am not responsible if you break anything

You can find the template script here

Enable CLR with Powershell

I had an email last night from someone who attended my PowerShell Box of Tricks session at SQL Saturday Exeter

He was getting an error whilst trying to set CLR Enabled during an automatic install and asked if I had any ideas. The error he had was related to Invoke-SQLcmd and the method he was calling the PowerShell script

I was unable to replicate his problem on my servers so I looked at other methods that may assist as well as following up with him to try and understand what was causing his issue. In doing so I worked out the following method to change the CLR Enabled setting by SMO and thought it worth a blog post to share

One way around his issue is to define and then call Invoke-SQLCmd2 by Chad Miller within his script. So his script would look in part as follows

However, I prefer to use SMO so I examined the Server SMO as follows notice the “.” for local server

$srv New-Object Microsoft.SQLServer.Management.SMO.Server .
$srv |gm

And noticed the Configuration property

$Config $srv.Configuration
$Config |gm

Enabled me to see the IsCLREnabled Property and using Get-Member I could see that the config value was settable

With this information I could write a simple script to alter the settings.

Prior to running the script

We then run the following script

Line 1 creates a Server SMO object there is a “.” to denote local server at the end of the line although you can use the server name as well

Line 4 sets the configvalue for the IsCLREnabled property

And Line 5 Alters the Config object, essentially running the reconfigure

After running the script

Hopefully this short post shows how easy it is to set SQL Server configuration values with Powershell using SMO

Any questions or comments please feel free to ask

Listing the SQL Server SysAdmins With PowerShell

A very short blog today just to pass on this little script.

I was required to list all of the SysAdmins across a large estate. Obviously I turned to PowerShell :-)

I iterated through my server list collection and then created a server SMO object and used the EnumServerRoleMembers method to display all of the sysadmin members


This will work on SQL2000 – SQL2012. You can see how you can easily change the rolename in the script to enumerate other server roles.

Another way you could do it is to use the query

“SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = ‘R’ AND a.name =’sysadmin’
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id”

and pass that with Invoke-SQLCMD through to every server (if you had to use Powershell :-) ). That query won’t work with SQL 2000 though

How I Check Hundreds of SQL Agent Jobs in 60 Seconds with Powershell

Checking that your Agent Jobs have completed successfully is a vital part of any DBA’s responsibility. It is essential to ensure that all of the hard work you have put into setting up the jobs can be quickly and easily checked. In a large estate this can be very time consuming and if done manually prone to human error. I have repeatedly mentioned John Sansoms Blog Post entitled “The Best DBAs Automate Everything” and I follow that advice. Today I will share with you one fo the first scripts that I wrote.

When I started as a DBA I was told that my first job every morning was to check the Agent Jobs and resolve any errors. This is still something I do first before anything else. (Except coffee, experience has taught me that you get your coffee before you log into your computer otherwise on the bad days you can miss out on coffee for many an hour) I have two scripts to do this. The first sends me an email if the number of failed jobs on a server is greater than zero. This helps me to quickly and simply identify where to start in the case of multiple failures and is also a backup to the second script.

The second script runs on a different server and creates an excel worksheet and colour codes it. This makes it very simple to quickly scroll through the sheet and spot any red cells which designate failed jobs and also provides a nice easy to understand method to show management that on that specific day everything went well (or badly)

As with any Powershell script which manipulates Office applications you first need to create an object and add the workbook and worksheet to it. I also set a filename date variable and a Date variable for the Sheet.


When you use Powershell to manipulate Excel you can access individual cells by identifying them by Row and Column. I use this to create a description for the work book as follows

There are lots of properties that you can play with within Excel. As with any Powershell the best way to find what you need is to use the Get-Member Cmdlet. If you run


You will see that there are 185 Methods and Properties available to you (in Office 2013 on Windows 8.1)

The snippet above creates the following


As you can see we are going to colour code the Job Status according to the three available results Successful, Failed and Unknown. We are also going to colour code the date column to see when the job was last run, this will enable you to easily identify if the last time the job ran it was successful but last night it didn’t kick off for some reason.

The next step is a fairly standard loop through available servers by picking them from a SQLServers text file, a list of the server names (ServerName\Instance if required) that you wish to check. You could also just create an array of server names or pick them from a table with Invoke-SQLCmd but which ever way you do it you need to be able to iterate through the array and then the .Jobs Collection in the JobServer Namespace as follows



What the script then does is to use the following properties of the $Job object and write the Excel File according to the logic in the description


To finish up save the workbook to a share available to all of the DBA Team and quit Excel. Notice that I use a double whammy to make sure Excel is really gone. First I quit the .com object and then I stop the process. I do this because I found that on my server quitting the .com object left the Excel process running and I ended up with dozens and dozens of them. If you have Excel open before you run this script either comment out the last line or save your work (You should save your work anyway regulary!)

As always I take no responsibility for your environment, that’s your Job! Don’t run this on Production unless you know what it is doing and are happy that you have first tested it somewhere safely away from any important systems. Make sure that you understand the correct time to run this job and have qualified the impact on the box it is running on.

Here is a screen shot of the finished Excel Sheet


As you can see the Data Transfer Job needs investigation! The reason I add to yellow rows above and below each servers list of jobs is to help me identify any server that is not responding as that will be easily recognised as two lots of yellow with nothing between them

I have considered improving this script by inputting the data into a database and running a report from that database but have not had the need to do so yet.

Enjoy this script, you can find it here


If you have any questions please use the comment box below