Small PowerShell Script

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

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.