It is a common and business requirement to backup the data. The archiving is part of back ups and most of the applications and system will be following some procedure to archive the data.

Since most of the application is having some kind of mechanism to archive data like SQL stored procedures, statements, Database bound applications to do the archiving.

I have seen having bigger and complex statements in SQL stored procedures to copy the data into archive tables based on some conditions, say created date etc.

But, the problem starts when we add tables or change structure of some tables. We often forget to update or create archiving tables / table structures, which intern will fail or miss out the newly created table or field data.

I was thinking to automate this process, so that, if any of the above said problems rises, the archiving process will continue.

I have created a stored procedure to do the same; this does the following things as validation and execution procedure

1.      Check whether the archiving table exists for the original table , if not, it will create a table with the same structure of the original table

2.      Check, whether the structure of both original and archiving table is same, if not, it will rename the old archiving table and create a new archiving table

3.      Check for, any identity column exists in the table. This will automatically identify the identity column name

4.      Change the identity feature of the column of archiving table. You need to remove the identity property, otherwise, the original table data cannot be inserted as is in archiving table

5.      Insert the data from original table into archiving table based on some condition

This stored procedure can be run as a SQL job and keep on running on a specified interval.

However, some enhancement in some functionality will help the procedure to its 100% fail proof, like

1.      As of now, to remove the identity feature (not the column), this will remove the identity column and add a column with the same name.

2.      Due to this, the original table identify column should be the last one. As add will add a column always last of the structure.

 The complete SQL stored procedure

declare @tablename varchar(500)
declare @sql varchar(5000)
declare @idname varchar(50)
declare @tablearchive varchar(500)
 
--Select all the tables which you want to make in archive
declare tableCursor cursor FAST_FORWARD FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
where table_name
 
--Put your condition, if you want to filter the tables
--like '%TRN_%' and charindex('Archive',table_name) = 0 and charindex('ErrorLog',table_name) = 0
 
--Open the cursor and iterate till end
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tablename     WHILE @@FETCH_STATUS = 0
      BEGIN
             set @tablearchive =  @tablename+'Archive'
             --check for the table exists, not, create it
            IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME= @tablearchive)
                  begin
                        SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
                        EXEC(@sql)
                  END
             --check the structure is same, if not, create it
            IF exists (select column_name from
            INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablearchive))
            begin
                  SET @sql = 'drop table ' + @tablearchive
                  EXEC(@sql)
                  SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
                  EXEC(@sql)
            end
             --Check if the table contains, identify column,if yes, then it should be handled in different way
            --You cannot remove the identity column property through T-SQL
            --Since the structure of both tables are same, the insert fails, as it cannot insert the identity column
            --value in the archive table
            IF EXISTS(SELECT *      FROM information_schema.tables      WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') != 0)
                  BEGIN
                        --Select the identity column name automatically            
                        select @idname = column_name  from
                        information_schema.columns where
                        columnproperty(object_id(table_name),column_name,'isidentity')=1
                        AND table_name = @tablearchive
 
                        --Remove the column
                        SET @sql = 'ALTER TABLE ' + @tablearchive + ' DROP COLUMN ' + @idname
                        EXEC(@sql)
 
                        --Create the column name again (not as identity)
                        --archive table does require identity column
                        SET @sql = 'ALTER TABLE ' + @tablearchive + ' ADD ' + @idname+ ' INT'
                        EXEC(@sql)
                  END
  
                  SET @sql = 'insert into ' + @tablearchive +' select * from '+ @tablename
                  EXEC(@sql)
     
      FETCH NEXT FROM tableCursor INTO @tablename
END
CLOSE tableCursor
DEALLOCATE tableCursor
 

Any suggestions are welcome to improve this..