Thursday, December 10, 2009

PowerShell

One of the new features to surface in SQL Server is PowerShell. It is a scripting lanaguage which is very powerful altrnative for VB script/SQL-DMO. PowerShell has lot of commands and functions which are very useful for database connectivity,querying and file movement and copying of files. The Powershell is also incorporated into SQL Server Jobs as a type which means that Powershell scripts can be executed with a sql server job. Once logged into SSMS, one can start the PowerShell by right clicking on the databases, this will take to the PowerShell Prompt, which looks likes a DOS-Command prompt window. In this window, Powershell commands can be executed, this can be used as a good testing ground for building PowerShell scripts. Here is a link to an excellent link on PowerShell tutorial:
http://www.powershellpro.com/, the lessons are very well laid out with lots of examples:

Here is a sample of how a Powershell script will look like: For example, if the need is to delete files which are more than 3 days old:


$Now = Get-Date
$Days = "3"
$TargetFolder = "Your Path Where the Files are Located"
set-location $TargetFolder
$ModifiedDate = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolderWhere{$_.LastWriteTime -le "$ModifiedDate"}
If($Files -eq $Null)
{exit}
Else
{
ForEach($File in $Files)
{Remove-Item $File}
}

No comments:

Post a Comment