In my environment I have lots of databases being log shipped. To see the current status of the log shipping I either have to execute a procedure or execute a standard report from SSMS. And I know there are other solutions but in most cases it comes back to these two.
The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds. This doesn’t show me the problems right away but it’s fast.
The report shows me in nice green and red colors which which is good and what’s not but I’d have to do it for every server manually. So it’s pretty but it’s slow.
Why isn’t there anything that has the best of both worlds where I have the speed of the query with the clear results of the report?
That’s where Pester comes in!
I can use Pester to test the values that come back from the procedure and do basically the same thing the report does.
The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds.
There is also an overall value called “status” which shows a 0 when everything is fine and a 1 when something is wrong.
I wanted to keep the check as simple as possible. If everything is fine, I don’t need to go into much detail. If there is something wrong I want to know which part of the log shipping failed, like the backup, copy or restore.
The Overall Test
This test only looks at the the marker the log shipping sets to indicate if everything is OK or not.
.\LogShipping.Tests.ps1 -SqlServer sql1
Ooh oh! Something failed. Good thing we have our test.
The Detailed Test
The detailed test goes into the detail of the last backup, last copy and last restore time. We want to know what went wrong and where.
In this case I only want to zoom in on the database with the failed test so I use the -Database parameter to give the name of the database.
.\LogShipping.Tests.ps1 -SqlServer sql1 -Database db1 -Detailed
Of course it’s possible to execute the detailed test for all the databases. Just remove the database parameter and the same tests are run for all the databases.
The entire test took less than two seconds and made it possible for me to quickly see if everything is fine with the log shipping.
The code can be downloaded from github. I hope this helps you out and make your life a little easier as it did for me.