Small PowerShell Script

July 6, 2010 at 9:59 am (PowerShell, SQL Server 2008, SQLServerPedia Syndication) (, , , )

I’m still trying to learn PowerShell better. The opportunity to answer simple questions and problems with the tool is hard to pass up. We had a need to clean up data directories where data files were left behind or people put inappropiate files, so I wrote the following Powershell script:

param([string]$filelocation="",[string]$sqlinstance="(local)")

Set-Location $filelocation

foreach($file in get-childitem)

{$base = $file.Name;

$result = Invoke-Sqlcmd -ServerInstance $sqlinstance -Query "SELECT DB_NAME(mf.database_id) AS db FROM sys.master_files mf WHERE RIGHT(mf.physical_name,LEN('$Base')) = '$Base' UNION ALL SELECT 'NoDb' AS db WHERE NOT EXISTS (SELECT DB_NAME(mf.database_id) AS db FROM sys.master_files mf WHERE RIGHT(mf.physical_name,LEN('$Base')) = '$Base');" ;

if($result.DB -eq "NoDb" -and $file.Extension -ne ".cer"){Remove-Item $base}}

It’s a very simple script. It takes a UNC and a server instance and then walks through the files in the UNC and validates whether or not those files exist within databases on the server. If they don’t exist, it deletes them. That’s it.

I’ve published this to the Technet Script Center Repository right over here. I’m going to work on making it a bit better, so for updates, go there.

Permalink 1 Comment

SQL Server 2008 Management and Administration

February 3, 2009 at 8:50 am (SQL Server 2008) (, , , )

I just got a book in the mail from a friend, Todd Robinson, who was the technical editor. The name of the book is Microsoft SQL Server 2008 Management and Administration. The book was written by Ross Mistry and Hilary Cotter. I don’t know Ross Mistry and I think I met Hilary Cotter once, although I know who he is. However, since Todd was involved, I’m pretty sure this is going to be a high-end, must read. I just started and the initial chapter’s discussion on using Windows Server 2008 sparked a few questions for my local admin team. I’m looking forward to more.

Thanks Todd.

Permalink 2 Comments