CODEWITHSUNDEEP
sqlsql-server
Conrad Jagger
Conrad Jagger

Reputation: 2333

SQL Server - Archiving Data (Strategy/Stored Procedure)

Currently we have 100+ databases, some about 10GB in size 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 archives the data into an ARCHIVED database - with everything being audited (in regards to number of records imported etc..) and in case of failure it rolls back everything?

Regards

Upvotes: 4

Views: 1841

Answers (2)

Stoleg
Stoleg

Reputation: 9320

Partitioning can help a lot to archive within single database. Sliding window scenario is a particular tool.

Let me suggest setting up an Admin database. It will handle all setting and information about archiving.

There may be 2 SQL Server instances: Current Server and Archive Server. They will have same structure.

Process copies data from remote server to archive server using settings from Admin DB. There may be need in writing Dynamic SQL. Check Sp_MSForEachDB.

Upvotes: 1

athabaska
athabaska

Reputation: 455

+1 to partitioning idea. To add - I think you can use it also if you have Developers edition

Upvotes: 0

Related Questions