Solved

sql server 2008 Archive table

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

19 Experts available now in Live!

Get 1:1 Help Now