Listing SQL Server Instances

As a consultant I go into environments where the client simply does not know how many SQL Server instances they have. Perhaps as a DBA in a company you've been asked to do a license audit and need to know about all the SQL Server instances in your company. Or, as is common in New Zealand so probably elsewhere, you've just started at a company as their first DBA since they've grown to a size that requires one. The same company has probably allowed too many people to have too many rights and all kinds of SQL Server instances have sprung up all over the place.

Its a common situation. You can of course physically go to each machine in your company and check what is installed. But I'm a lazy DBA, or I try to be, and I want a solution that can let me have another coffee before lunch time.

If you do a Bingoogle search for something like "list sql server instances" you'll probably find these articles:

• http://thepowershellguy.com/blogs/posh/archive/2007/02/09/powershell-get-sqlserverlist.aspx
• http://sqlvariant.com/wordpress/index.php/2010/09/finding-sql-servers-with-powershell/

Both of which list this PowerShell command:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

I did. And when I did all I could think was "Sweet! How easy is that!?"

Sadly not as easy as it looks.

Deleting files by age with PowerShell

Back in November 2011 I blogged about using FORFILES to delete files by age.  I've since been "playing" with PowerShell and intend to blog quite a bit about it over the coming months.

To say PowerShell is powerful is an understatement!  There's plenty of material on the Internet to help you too.  But I wanted to kick off by replacing that old article with a PowerShell script, basically because I find PowerShell far easier to read!

But rather than just dumping a command with little explanation, I want to work you through it. This also demonstrates how I think through a problem with PowerShell and maybe that'll help you too. So I start with trying to display the items I want to deal with (be it report on, delete, move, action etc):

Get-ChildItem "C:\Example"

That will list all the files in C:\Example directory. A bit like this:

Mode                LastWriteTime     Length Name                                                                       
----                -------------     ------ ----                                                                       
-a---       7/02/2012  11:41 p.m.        598 File A.txt                                                                 
-a---       7/02/2012  11:41 p.m.         95 File B.txt                                                                 
-a---       7/02/2012  11:41 p.m.        311 File C.txt                                                                 
-a---       7/02/2012  11:41 p.m.         12 File D.dat   

Note: You can use DIR too as it is aliased to Get-ChildItem.

Ok...but we're interested in files older than an age.