Solved

sql server 2008 Archive table

Posted on 2013-06-07
4
260 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 26

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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 demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

17 Experts available now in Live!

Get 1:1 Help Now