Share via

Dynamic archiving of Tables and Data: MS SQL

  • Article

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..