Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Archiving Data in SQL Server

By:   |   Updated: 2006-11-28   |   Comments (7)   |   Related: More > Database Administration


Every system has it, do not feel like you are the only one.  Step up to the plate and correct it.  That is large amounts of data that were once needed for the business that can now be archived.  This data could be a large performance issue, because you are not able to perform maintenance on the table(s) because the time needed exceeds your maintenance window.  As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted.  Many industries have legal requirements to maintain the data.  On the other side of the coin though, just because a business person says the data is needed does not mean it has to be kept in the primary OLTP database that your entire company uses.  Seek out the facts and determine which strategy is right for your organization.


When it comes to archiving data, many options are available, but some preliminary items need to be addressed.  These items include:

  • Research the legal requirements - Make sure you understand your industry requirements, some have no data retention requirements while others require 7 years.  Make sure you understand your requirements first and then design a solution.
  • Data analysis - Review the data in your application to determine the key tables and dependencies between the tables when archiving.  Be sure to also capture the distribution of data on a monthly, quarterly or yearly basis to understand how much data can be archived.
  • Understand the data usage - Meet with the users to understand how they use the potentially archived data and if it is archived how the applications will need to change.  If you are not responsible for the application development, ensure you meet with the development team to ensure the changes are feasible.
  • Provide a business case - Justification for archiving the data is essential when taking on this project.  These items are typically attractive to business and IT management:
  • Automate the data archiving process - Setup rules with the users to be able to automate the archive process so it occurs regularly on a weekly, monthly or quarterly basis

Once you understand how the data can be archived, now the technical decisions need to be made.  Depending on the answers to the questions above, one or a combination of the following approaches can be taken:

  • If the data needs to reside in the same database:
    • Move (insert and delete) the archive data to separate table(s) with a prefix to denote the tables are archive related such as 'arc_'
    • Create the table(s) in separate filegroups on separate disks to improve IO performance
    • Use a view to join the old and new data if the users continue to need to access the data
    • Modify the front end code to use the view rather than the base table
  • If the data does not need to reside in the same database:
    • Move (insert and delete) the archive data to separate table(s) in another database on the server or to a separate database on another server
    • Have users request access to the data for specific queries or change the application to use a linked server to access the archived data
  • If the data is just not needed:
    • Back up the data, verify the backup is restorable, note the date of the last backup with the archived data and just delete the data
    • If the data is needed due to some unforeseen reason by the users, restore the noted backups to access the data
  • Automation options
    • Based on the application find out if the vendor has already implemented an archiving feature that just needed to be enabled.
    • If an automated option is not available, build a process with DTS or SSIS to move the data from the source system to the archive system. 
    • If significant amounts of data are changed, be sure to perform SQL Server maintenance as a portion of the archive process.
Next Steps

Last Updated: 2006-11-28

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Tuesday, September 02, 2014 - 12:54:27 PM - Hiren Patel Back To Top

Very good article with key information, thank you!

Thursday, September 19, 2013 - 11:04:10 AM - ASIF GHANCHI Back To Top

Please check below link to configure auto archive and purge in sql server table.


Friday, February 01, 2013 - 7:45:54 AM - Nandkumar Back To Top

Thanks for the grat information really it helped

Thursday, November 29, 2012 - 4:23:44 PM - Jeremy Kadlec Back To Top


Thank you for the additional information.

Thank you,
Jeremy Kadlec

Thursday, November 29, 2012 - 11:12:48 AM - Lelala Back To Top

Hi Jeremy,

basicly its a special way of storing the data for long term use in a very special table, but you can't change it anymore (maybe they compress it or similar). The idea is very nice, because for things like statistics this implementation is ideal:

I've looked for their original dokumentation post, please:


I'm familiar with the idea  of "backup-tables" in MSSQL, but we use standard MSSQL-tables to achieve what we want - the MySQL thing seems a little bit more performant and uses less space than what we can achieve with MSSQL (and no, i do never use server-side full-DB compressions, because this is the end, if you have a disk crash or similar)



Wednesday, November 28, 2012 - 1:16:18 PM - Jeremy Kadlec Back To Top


Thank you for the post.  I am not familiar with the functionality in MySQL.  Can you please explain it?

Thank you,
Jeremy Kadlec

Wednesday, November 28, 2012 - 11:09:47 AM - Lelala Back To Top

Thanks for that interesting post;

i'm wondering, why does MSSQL do not support "archive tables" as we can have it in MySQL, for example? Would be very cool from Microsoft, if they would include something like this!




Learn more about SQL Server tools