How to archive big table in prod envrmnt in SQL server azure

Question

Hi All, I have a database in production environment, i have 3 big tables, 1 table around 300 gb, 2nd table around 100 gb 3rd tbale around 50gb. I need to do two tasks: 1. Take backup of these tables. 2. Remove previous years data from the table in prod environemt (tbale have date column to filter data) Points: DB or table should not lock for long time while backup or removing data as DB is in prod environment. DB is azure SQL database, i am using SSMS 2019. looking for best possible approach. thanks

Salman Beg · Answer

Alright. So create a job which will run in the background and by using that job start transferring the record to some database table and delete the record from current table. If you are using azure then you can use azure webjob. Thanks.

Hrides Thakur · Answer

Hi @Salman Beg , to take .bacpac backup there should not be read/write operation to get transactional consistant data, as i have alredy mentioned that DB is in production environment, and there would be many read/write operation and .bacpac bakcup take around 40-50 hours to take the backup of around 500 gb DB (we have done POC). Regarding delete, " delete command in prod.It will take few minutes i think." no it would not take few minutes, here i am takinng about many millions of row deletion. Thanks for the comment.

Salman Beg · Answer

In Azure, it uses .bacpac file for backup and restore. First take backup of the database by using this article, https://www.c-sharpcorner.com/article/backing-up-and-restoring-database-without-using-bak-file/ Delete the previous year table data by using delete command in prod.It will take few minutes i think. As you have a backup of the whole db so you can restore anytime. Thanks.

How to archive big table in prod envrmnt in SQL server azure

Answers (3)