Solved

sql server 2008 Archive table

Posted on 2013-06-07
4
272 Views
Last Modified: 2013-06-07
I have 2 tables that were created last year for a process that runs once each year. These tables need to be cleared before we run the process this year. I have been told we should "archive" the tables. How would I do this/ What would be the process?
0
Comment
Question by:qbjgqbjg
[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 75

Expert Comment

by:Aneesh Retnakaran
ID: 39229893
if you are using sql 2008, the best option is to go for Partitioning by year. that way it is easy to moveinto another table or you can leave as it it. it wont affect performance of your query at all as it is on a different partition.

http://blog.sqlauthority.com/2008/01/25/sql-server-2005-database-table-partitioning-tutorial-how-to-horizontal-partition-database-table/
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39230153
The easiest way would be to do this:

1. Script the table structure but make sure you also script its indexes. To do that you will go in MS and right click on the database name > Task  > Generate Scripts > Next > Select Script specific... > expand Tables node > check the table(s) you need to script > Next > choose Save scripts to a specific location and below Save to file and Single file per object(if you have multiple tables) - choose a location folder > click on Advance button > in the bottom section called Table/View options make sure you set to True indexes, triggers, Foreign Keys but NO DATA!> click OK > Next > Next > wait until done > click Finish

2. Rename the tables that you scripted. Right click on them > Rename > give them name like table_YYYY, or whatever seems proper to you.

3. Open the scripts generated in step 1 and run them.

Done.

I agree that the partitioning will be a nicer solution but if you've never done that I would recommend to first test it and only when you are sure how it's done to implement it.
0
 

Author Closing Comment

by:qbjgqbjg
ID: 39230170
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is 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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

636 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