What is the best way of data archiving in sql server?

[l***@gmail.com]

We have database in SQL sever with huge data. So we are planning to archive the data.
Please any one can suggest me the best way of data archiving in sql server.


Regards,
Lalit

[Erland Sommarskog]

Post by l***@gmail.com
We have database in SQL sever with huge data. So we are planning to archive the data.
Please any one can suggest me the best way of data archiving in sql server.

That's a question which is impossible to answer without knowing your
exact requirements. Here are some options:

1) Just delete the data.
2) Take a special backup of the database and save it it somewhere before
you delete the data.
3) Restore the aforementioned backup on some old server with some old
disks you had intended to throw out. Then delete the data.
4) Restore the aforementioned backup on the cheapest Azure VM you can
find. (Or at Amazon or any other cloud provider).
5) Use stretch database introduced in SQL 2016 (horridly expensive, I am
told.)
6) Use partitioning and put the stale data on a file group that you
make readonly, and in the future you only backup the live filegroup
most of the time.

There are probably more options, but I'll stop now.