Thursday, March 16, 2006

VBScript delete files in a folder that are certain days old

Using VBScript to Automate Tasks: "VBScript to delete files in a folder that are certain days old
As part of a disaster recovery plan, you may need to transfer backup files from one server to another. However, you probably don't want to keep accumulating backup files that you run out of space on your backup server. In this case, you want to delete files that are certain days (or weeks) old. The following VBScript can handle this task nicely. You can customize this code, such as changing the value of iDaysOld, to fit your needs. Most of the code should be self-explanatory. For more on disk space management, see one of my articles published a couple of weeks ago.
Option Explicit
on error resume next
Dim oFSO
Dim sDirectoryPath
Dim oFolder
Dim oFileCollection
Dim oFile
Dim iDaysOld

'Customize values here to fit your needs
iDaysOld = 21
Set oFSO = CreateObject("Scripting.FileSystemObject")
sDirectoryPath = "FolderName here. Can be UNC path like \\MyServer\MyFolder"
set oFolder = oFSO.GetFolder(sDirectoryPath)
set oFileCollection = oFolder.Files

'Walk through each file in this folder collection.
'If it is older than 3 weeks (21) days, then delete it.
For each oFile in oFileCollection
If oFile.DateLastModified < (Date() - iDaysOld) Then
oFile.Delete(True)
End If
Next

'Clean up
Set oFSO = Nothing
Set oFolder = Nothing
Set oFileCollection = Nothing
Set oFile = Nothing"

SQL Server 2000 Security - Part 11 - DTS Security

SQL Server 2000 Security - Part 11 - DTS Security: "You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs."