Table Archive

Question

I have a table with 4 million rows. I want to back it up as an archive. what are the methods?

Answer 1

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_' or 'hist_'. 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. Have users request access to the data for specific queries or change the application to use an elastic query to access the archived data

If the data is just not needed: Back up the data into dacpac, verify the backup is restorable (can be imported in to a new Azure SQL Database), note the date of the last backup with the archived data and just delete the data

You can automate the archiving of the data using Azure Data Factory or using Azure Automation.