Archiving Data in SQL Server

By:   |   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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 2, 2014 - 12:54:27 PM - Hiren Patel Back To Top (34353)

Very good article with key information, thank you!

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

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

Friday, February 1, 2013 - 7:45:54 AM - Nandkumar Back To Top (21862)

Thanks for the grat information really it helped

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


Thank you for the additional information.

Thank you,
Jeremy Kadlec

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

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 (20603)


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 (20597)

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!




get free sql tips
agree to terms