Archival Process in Database.

As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted. Many organizations have legal requirements to maintain the data.  Such kind of data we cannot directly delete from table. 

Here, in such a case Archival Process needed.

While Archiving data, some Preliminary items need to be Addressed.

• Data Analysis - Need to review the data in your application to determine the key tables and dependencies between the tables when archiving.  Be sure to also capture the distribution of data on a monthly, quarterly or yearly basis to understand how much data can be archived.

• Understand the Data Usage - Meet with the users to understand how they use the potentially archived data and if it is archived how the applications will need to change.  If you are not responsible for the application development, ensure you meet with the development team to ensure the changes are feasible.

• Justification for archiving the data is essential when taking on this Project.

• Automate the Data Archiving Process - Setup rules/SQL Job with the users to be able to automate the archive process so it occurs regularly on a weekly, monthly or quarterly basis as per the requirement.

Example :

According to need I have to keep only recent 6 months data in table. Other data should be archived.

Following Stored Procedure will use to achieve the above requirement.

CREATE PROCEDURE [dbo].[SP_ArchiveAll]
AS
BEGIN
DECLARE @Error INT

BEGIN TRANSACTION

  INSERT INTO FC_FLT_EntryArchive
   -- this is archival table
  (
    Site_ID,Status_Code,Active_Code,Priority_ID,ORS_ID,Lang_Code,NumPages,Comments,Created_On
  )
  SELECT Site_ID,Status_Code,Active_Code,Priority_ID,ORS_ID,Lang_Code,NumPages,Comments,Created_On
  FROM FC_FLT_Entry
  -- give the appropriete condition in where clause as per need
  WHERE YEAR(Created_On) <=  YEAR(DATEADD(MONTH, - 6, GETDATE())) AND
              MONTH(Created_On) <  MONTH(DATEADD(MONTH, - 6,GETDATE()))

  -- delete data from main table
  DELETE FROM FC_FLT_Entry
  WHERE YEAR(Created_On)<=YEAR(DATEADD(MONTH, - 6,GETDATE())) AND
        MONTH(Created_On)< MONTH(DATEADD(MONTH, - 6,GETDATE()))     
 
  -- keep the log to archival process
  INSERT INTO uhc_Archive_log(LogDate, Action,TableName, RecCount)
         VALUES(GETDATE(),'Delete','FC_FLT_Entry', @@rowcount)   
 
  -- following code capture if any error occures
  SET @Error = @@ERROR
  IF @Error <> 0 
    BEGIN
     GOTO LogError
    END
 
  COMMIT TRANSACTION
  GOTO ProcEnd 

  LogError:
  ROLLBACK TRANSACTION
 
  DECLARE @ErrMsg VARCHAR(500)
  SET @ErrMsg = 'Undefined Error'
  SELECT @ErrMsg = [description] FROM MASTER.dbo.sysmessages WHERE error = @Error
  INSERT INTO uhc_Archive_error_log (LogDate,Source,ErrMsg)
         VALUES (GETDATE(),'SP_ArchiveAll',@ErrMsg)
 
  ProcEnd:
END