question

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?

Hi @HaniQaisieh-3493，

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.

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.