All geek questions in one place

SQL Server - Data Archiving (Strategy / Stored Procedure)

We currently have over 100 databases, about 10 GB with millions of records, and they are growing at an alarming rate. We need to evaluate our archiving strategy.

Does anyone have any suggestions and sample scripts that go through all the tables and archive the data into the ARCHIVED database - everything is checked (regarding the number of imported records, etc.), and in case of failure it rolls back everything?

Hello

+4
source share
2 answers

Partitioning can help with archiving in a single database. The sliding window script is a special tool.

Let me suggest setting up an admin database. It will process all settings and archiving information.

There can be two instances of SQL Server: the current server and the archive server. They will have the same structure.

The process copies data from the remote server to the archive server using the settings from the administrator DB. You may need to write Dynamic SQL. Check out Sp_MSForEachDB .

+1
source

+1 to the idea of ​​breaking. To add - I think you can use it also if you have a developer version

0
source

Source: https://habr.com/ru/post/1382684/

More articles:


All Articles