Personal Weblog of John Wood

Don’t you just hate it when you open one of your server logs in Management Studio and then you have to wait forever to review it because it never stops reading? I’ve done it enough times and decided to put an end to it or at least greatly reduce the incidence.

Using Powershell and a simple script to execute Sp_Cycle_Errorlog, I now have a weekly, Windows scheduled task which executes the sproc to cycle the logs for all my SQL Servers.

foreach ($svr in get-content D:\Scripts\Servers.TXT ){
    $svr
    Invoke-Expression 'SQLCMD -E -S $svr -Q "Exec Sp_Cycle_Errorlog"' | Out-Null
	}

As a databases admin with a few dozen servers and a few hundred SQL Server database, I need to effectively build automated jobs to perform various administrative tasks such as backing up databases. I’m still new to PowerShell but I am beginning to see how immensely helpful it is to me and my work.

I found a good PowerShell script for backing up databases at this blog location. However, I wanted to change it slightly to accommodate backing up ALL, System, User, or a list of databases. Processing ALL, System, or User is straightforward enough but I had to it was the first time I came up against have to compare a value against a list of values. In SQL you simply use an IN operator with a list of values but I was not sure of how to accomplish the same process in PowerShell.

Here was my problem: the user could supply a list of databases to backup and the backup script has a line with captures all the database objects for that SQL Server instance. I need to backup only those which are in the users list.

I found that the PowerShell WHERE clause can use a -contains operator which effectively filters the selection for an array. To test the operation I set up a small script to build two arrays and then I piped one array through a WHERE filter of which the results are piped to a ForEach. The ForEach sees only the results of the WHERE which mimics the SQL IN operator.

Run this code in PowerShell to see what I mean.

$a = @("db1","db2","db3","db4")
$d = @("db1","db2","db3","db4","db5","db6","db7","db8")
$d | where { $a -contains $_ } | foreach { Write-Host $_ }

$a represents the list of database supplied by the user.
$d represents the array of databases returned when accessing the Microsoft.SqlServer.Management.Smo.Server object.
I can now back up the user supplied databases within the foreach script block.

This is a wonderful article which not only details the use of Compare-Object but also provides some very excellent examples on its use.

Tips & Tricks Using Compare-Object – Dreaming in PowerShell – PowerShell.com

I am very new to PowerShell but I am beginning to like using it considerably more each day. I recently had a need for a quick and easy way to script a process which would copy only new files from one location to another. after scouring the Internet for a good example, I stumbled upon the Compare-Object cmdlet and this excellent article.

The actual script I came up with is rather simple and serves my purpose exactly. As I  mentioned, I am new to PowerShell and there may very well be a better method. However, this one works for me.

The script code:

param(
    [string]$s = '\\serverx\c$\ProgramData\Polaris\3.5\SQLVS1\AuthorityUpdates',
    [string]$t = '\\servery\data03\shared\libraries\zmarc'
)

$target = Get-ChildItem $t
$source = get-childitem $s
Compare-Object $source $target -Property Name -PassThru |
    Where-Object { $_.SideIndicator -eq '<=' } |
    foreach-object -process{
        copy-item $_.FullName -destination $t
        }

I write very small console apps which run on different servers, some of which cannot yet be upgraded from .NET 1.1 to 2.0.

Why Visual Studio targets only one version of the .NET Framework