Solved

How to archive table in SQL Server

Posted on 2009-05-04
4
2,089 Views
Last Modified: 2012-05-06
How to archive table in SQL Server every 3 months?
0
Comment
Question by:easy_life
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

697 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