As you probably know if you read my blog regularly, I recently put together a presentation called, Dive into the Default Trace, and as part of that presentation I wanted to provide examples of different ways to archive the Default Trace files.  Here are the option I considered:

1. fn_trace_gettable to load the data into a table.
2. SSIS to archive the files.
3. PowerShell to archive the files.
4. xp_cmdshell and the copy command.

I immediately eliminated #4 because I don’t have xp_cmdshell enabled and because Chad Miller (@cmille19) had said in his Powershell talk that anything that requires xp_cmdshell is a good candidate for Powershell.  So I sent Chad an email asking him where to start and he was kind enough to help out.  I got a “working” script together and sent it off to Chad for review and he kindly told me my errors (there were several) and included a modified script that cleaned up my code.  I did make a couple of minor modifications after receiving the script from Chad.  The idea and starting point were mine, even if much of the finished product is Chad’s.  So here’s the code (please comment if you have any improvements):

# Written with much Assistance from Chad Miller 
# (http://chadwickmiller.spaces.live.com/Blog)
param ($serverName)

#######################
function Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'),
            [string]$databaseName=$(throw 'databaseName is required.'),
              [string]$query=$(throw 'query is required.'))

Write-Verbose "Get-SqlData serverName:$serverName 
                    databaseName:$databaseName query:$query"

$connString = "Server=$serverName;Database=$databaseName;
                    Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt

} #Get-SqlData

#######################
function Get-TraceInfo
{
param($serverName)

$qry = @"
SELECT
   RIGHT(T.path, CharIndex('\', 
   Reverse(T.path))-1) as current_file,                      
   Substring(T.path, 1, Len(T.Path) - 
       CharIndex('\', Reverse(T.path))+ 1) as trace_path
FROM
    sys.traces T 
WHERE
    T.is_default = 1
"@

Get-SqlData $serverName 'master' $qry

} #Get-TraceInfo

#######################
# MAIN
#######################

if ($serverName -eq $null)
    {
$serverName = $env:COMPUTERNAME
}

$result = Get-TraceInfo $serverName
$copy_path = $result.trace_path + 'Archive' 

if ((Test-Path -Path $copy_path) -eq $false)
    {
New-Item -Path $result.trace_path -Name 'Archive' -ItemType directory
}

Get-ChildItem -Path $result.trace_path -Exclude $result.current_file | 
where {$_.name -like "*.trc"} | Move-Item -destination $copy_path

Once I knew what cmdlet’s I needed the PowerShell was probably the second easiest one to implement.  I know SSIS fairly well and it got a bit complex because of having to exclude the file currently in use.  Using fn_trace_gettable is simple because it allows me to use T-SQL which I have many years experience in.

Using PowerShell is really a matter of knowing what cmdlet’s you need, once you have that, the built in help (get-help) is very good, so you can just about do anything.  PowerShell is very powerful and flexible.  It can actually do so much that I think you need to be careful.  For example, the New-Item cmdlet can create just about any item depending on the ItemType you use.  '

So while I definitely recommend that any DBA learn some PowerShell I also recommend being very careful and Test, Test, Test!