Solved

How to archive table in SQL Server

Posted on 2009-05-04
4
2,084 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now