Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server 2008 Archive table

Posted on 2013-06-07
4
Medium Priority
?
278 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
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 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 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