asked on

SQL 2008 - Timely archiving of data in Tables

Hi,

I have got a database which has got 100+ tables. I want to create an ARCHIVE DATABASE and timely move the data from one database to another database.

Is there any thing available or has somebody got any code - which helps me to kick start

Objective is to:
->create an ARCHIVE DATABASE (which should have all the tables from the MAIN DATABASE)
->set an interval and based on that archive process should work (means any data more than 1 months old would be archived)

Regards

graye

This is something that I think you'll have to do with code (either via a program or via a stored procedure)...
A typical database doesn't have a universal way to determine the age of a record in a table. So, that means you'll need your own technique for determining the age (most likely some existing "date stamp" field)

lundnak

--Lots of methods. See below of one method.
--Please excuse any syntax errors.

create table tables_to_archive
   tablename varchar(500) not null,
   datefield varchar(100) not null,
   archiveage int)
 
alter table tables_to_archive add primary key pk_tables_to_archive (tablename,datefield)
 
insert into tables_to_archive
   select 'orders','orderdate',31
insert into tables_to_archive
   select 'invoices','invoicedate',31
 
--execute this procedure at the desired frequency.
create procedure p_archive_tables
as
   declare @tablename varchar(500),
           @datefield varchar(100),
           @archiveage int,
           @sqlcmd varchar(2000),
           @archivedate datetime
 
   declare tbl_cur cursor for
      select tablename, datefield,archiveage
      from tables_to_archive
      order by tablename
 
   fetch next from tbl_cur
      into @tablename, @datefield, @archiveage
 
   while @@FETCH_STATUS = 1
   begin
      select @archivedate = dateadd(dd,getdate(),-1 * abs(@archiveage))
 
      select @sqlcmd = ' insert into archivedb..' + @tablename +
                       ' select * from ' + @tablename +
                       ' where ' + @datefield + ' <= ''' +
                             convert(varchar, @archivedate, 101) + ''''
      exec (@sqlcmd)
 
      select @sqlcmd = ' delete from ' + @tablename + 
                       ' where ' + @datefield + ' <= ''' +
                             convert(varchar, @archivedate, 101) + ''''
      exec (@sqlcmd)
 
      fetch next from tbl_cur
         into @tablename, @datefield, @archiveage
   end

Open in new window

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Oh, and step 3 also included the time scales to hold data in live - that did take quite a bit of discussion with the business...