Solved

How to archive table in SQL Server

Posted on 2009-05-04
4
2,088 Views
Last Modified: 2012-05-06
How to archive table in SQL Server every 3 months?
0
Comment
Question by:easy_life
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 24301032
This is kind of an open-ended question, but I would use a SQL Agent job, schedule it to run nightly, and have it call a procedure that takes the data from your table that is over 3 months old and puts it into an archive table of the same structure.  

procedure code:

create procedure usp_archivedata
as
begin
insert into archivetable(field1, field2, field3)
select field1, field2, field3
from tablename
where datefield <= dateadd(m, -3, getdate())
end
go


information on sql jobs:
http://msdn.microsoft.com/en-us/library/ms191439.aspx
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 24301057
here is another option, partition the table  (only available on Enterprise edition
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24301306
chapman is absolutely correct, sql job is a good option, you can use SSIS packages as well.
0
 

Author Closing Comment

by:easy_life
ID: 31577836
Thank you for your help.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
Text file into sql server 5 32
SQL Stored Proc - Performance Enhancement 15 55
SQL Server 2008 R2, need a pivot/cross tab query... 4 29
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question