Dynamic Archiving of Tables and Data in MS SQL Server

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.

Most applications have some kind of mechanism to archive data like SQL stored procedures, statements and Database bound applications to do the archiving.

I have seen bigger and more 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 in turn 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 problems arises, 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 are same; if not, it will rename the old archiving table and create a new archiving table
3. Check that 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 the 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 be 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 exsits, 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 differet 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 idenfity 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.