Have you ever wondered how to control the amount of data preserved in the local file system of a production server? In this article, we will discuss a step-by-step implementation of a solution to purge files from a production environment based on a certain number of backup files in a target folder.
Background
As a DBA, one of the challenges we face is determining the best way to manage the amount of data stored on a production server. Should we purge backups based on date or by the number of files in a folder? The answer depends on the specific scenario and data load schedule.
If the data loads occur consistently at a specific time, we can easily purge backups based on date. However, if the data loads occur irregularly or infrequently, purging by date becomes less effective. In such cases, we need a solution that can purge files based on the number of backups in a folder.
Requirements
To implement this solution, we will need the following:
- A SQL job to call a DTS package
- A DTS package with global variables defined
- An ActiveX script task to purge files based on the number of backups
Implementation
Let’s start by creating the DTS package. The package should be designed to be reusable for different types of files in a target folder. We should also be able to update the number of files to keep variable depending on the client.
The DTS package will have only one ActiveX script task defined in it. This task will handle all the necessary operations using VBScript. Here’s a step-by-step guide to creating the DTS package:
- Open Enterprise Manager
- Expand the SQL Server Group, local server, and Data Transformation Services node
- Right-click on [Local Packages] and choose the New Package menu item
- Drag the ActiveX script task icon to the white space of your package
- Enter a short description in the field at the top of the scripting window
- Write your script (see the full listing at the end of this article)
- Save your package as type SQL Server or a Structured Storage File
Now that we have the DTS package, we need to make sure it’s ready to accept the global variables passed by the SQL job. Here’s how to prepare the package:
- With the package still open, right-click on the white space and choose [Package Properties]
- Click on the [Global Variables] tab
- Enter the name of each global variable that will be passed by the SQL job
- Check off “Explicit Global Variables” to force variable declaration
- Specify the variable types and values
- Click [OK] and save and close the package
Now we can create the SQL job to call the DTS package and pass the necessary variables. Here’s how to create the job:
- Open the Management, SQL Server Agent node
- Right-click on [Jobs] and choose New Job
- Define a short, meaningful name for your job
- Fill out a short description and choose a category for your job
- Choose the appropriate owner for the job
- Click on the [Steps] tab
- Choose [New Step] and fill in the necessary details
- Specify the command to call your DTS package
- Click [OK] to save and close the job
Finally, we need to write a VBScript to handle the file purging task. The script will perform the following steps:
- Get the global variables passed by the SQL job
- Open the target folder
- Create a collection of all the files in the folder
- Filter the files based on the specified file mask
- Sort the files in descending order based on the last modified date
- Delete all files except the most recent X number of files
- Log the variables passed and the files deleted to a text file
This process has been tested and works effectively. However, it’s recommended to test it on a development server before implementing it in a production environment.
Conclusion
Using DTS packages and global variables, we can extend the functionality of SQL Server to perform a variety of tasks efficiently. File purging is just one example of how DTS can simplify and automate routine tasks. By following the steps outlined in this article, you can implement a file purging solution tailored to your specific needs.