Table Archive

Rabia Kurnaz 386 Reputation points
2023-05-05T14:05:39.6766667+00:00

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

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. Alberto Morillo 33,251 Reputation points MVP
    2023-05-05T14:42:14.61+00:00

    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.

    0 comments No comments