SQL DBA : SQL 2005 - Move C2 Trace file from one Location to another Location

Move C2 Trace file from one Location to another Location :-

Create a Job With the Option of “ActiveX Script” (Select VB Script)

dim TracePath         ‘Declaring Variable for Assigning the Source path

dim FName              ‘Declaring Variable for Assigning the File Name

TracePath="G:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" ‘Source path of Trace File

   Set fso = CreateObject("Scripting.FileSystemObject")  ‘Object Creation for File system 

   Set folder = fso.GetFolder(Left(TracePath,len(TracePath)-1)) ‘Object Creation for Folder Access 

   Set files = folder.Files                                     ‘Object Creation For File Access

i=0

 For each fileIdx In files                     ‘Loop For Accessing the Files in the Source Folder

 FName = fileIdx.Name

              if right(FName ,4) = ".trc" then  ‘Checking the file Extension of the Trace file

                 Redim Preserve allFiles(i) 

                 Redim Preserve allTimeStamps(i)

                 allFiles(i)=fileIdx.Name       ‘Storing a Filename in a Array

                 allTimeStamps(i)=fileIdx.DateLastModified ‘Storing the Datemodified value of file

                      i=i+1

              end if

 Next

               'Now sort as per Modified TimeStamp  

 for i =  lbound(allTimeStamps) to ubound(allTimeStamps)

       for j =  lbound(allTimeStamps) to ubound(allTimeStamps)

              if allTimeStamps(i)<allTimeStamps(j) then

                     tt=allTimeStamps(i)

                     allTimeStamps(i)=allTimeStamps(j)

                      allTimeStamps(j)=tt

                      tt=allFiles(i)

                     allFiles(i)=allFiles(j)

                     allFiles(j)=tt

              end if

       next

 next

for i =  lbound(allFiles) to ubound(allFiles) -1        ' -1 so that we leave the latest file

       fso.movefile TracePath&allFiles(i) ,"P:\Audit\"  ‘Move the trace to Destination Path

Next

set fso = Nothing     ‘Clearing the Created Objects

set folder = Nothing

Set files = Nothing

Finally you can create a Step for Success and failure status mail for Mail Alert