Data Archiving and Purging

This repository contains the SQL Scripts to perform Data Archiving and Purging along with Proper updates via Email using Microsoft SQL Server.

Note: This is a Real Time project which I was working on for my internship as a Data Engineer Intern at Sagitec Solutions

Basic Definitions

Data Archiving: Data archiving is the process of moving data that is no longer actively used to a separate storage location for long-term retention. Archived data is typically retained for historical or compliance purposes.

Data Purging: Data purging is the process of permanently removing or deleting data that is no longer needed or has exceeded its retention period. Purging ensures that unnecessary data is removed from the system to free up storage space and improve system performance.

Features

1. Ready to use
   • The Scripts provided in my repository is completely reusable
   • Just Change the parameters of the procedure according to your usecase, and it is ready to use
2. Completely Dynamic (No Manual Intervention)
   • The SQL Scripts provided is completely dynamic, so no need of any manual intervention.
3. Automatic column addition and missing column management
   • Whenever a new column is added in the source table, the same column will also be added in the target table also.
   • If a column is removed from the source table, the script won't delete the column in the target table. Instead it preserves the previous data in the column
4. Email Notification after completing the Data Archival and Data Purging Operation
   • If the operation is completed, it notifies the user through the given Email ID along with some details of the operations such as Start Time, End Time, Source Table, Destination Table and so on
5. Maintains a Detailed Log Table
   • It also maintains a log table to have a track of the operations performed over time.
   • The Log Table has most of the information with respect to the operation as well as information about the Tables and the Data (Primary Keys Moved) as well

Tools Used

• Microsoft SQL Server 2022
• Microsoft SSMS (SQL Server Management Studio 19)

Acknowledgment

• This project owes its success to the invaluable assistance and direction provided by Mr. P Vengateswaran, Sr Manager at Sagitec Solutions.