Archiving old data in SQL Server

Question

Hi All ,

I have a large database with 3 tables each containing about 80 - 90 million records , these tables contain about 5 years of data .
We only need to keep the last 2 years of data only . ( we will keep the old data for later use ).

My question is : What is the best approach for archiving data in SQL Server ?

(SQL Server 2016 )

Many Thanks,
Hani

Answer 1

Hi @Hani Qaisieh ，

You can give preference to a second database:
You can restore the data from an historic backup then drop the tables and indexes you don't need.

You can move this to a different server for reporting purposes, this has the benefits of not using the resources of the primary server

You would still need to delete all the historic data from the primary database but this could be scheduled in.

Table partitioning is a great solution, but it is the most complex to implement:
https://www.sqlshack.com/archiving-sql-server-data-using-partitions/

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

build a process with 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.
https://www.mssqltips.com/sqlservertip/1121/archiving-data-in-sql-server/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What is the best approach for archiving data in SQL Server ?

First question is why do you want to? Performance issues? Out of free disk space? Where to you want to archive the data? In a separate data base and/or server?

Answer 3

We need an archiving solution because our customer asked to hide old data from the reporting system , and they only need to see the last two years ,, they need to keep the old data for later use . I think that ,modifying the reporting system is not an option .. as we have more than 50 reports .

Here is a dirt simple solution. Rename the tables, or move them to a different schema. Then create views with the same names as the tables, but which filters out the data the customer do not want to see.