June 10, 2014 at 9:51 am
Hi
I'm exploring the different possibilities/means that are available to allow me to archive part of my database.
The scenario I'm working under would be a script which would copy/insert data older than x (per table) over to a copy of the database located on a different server, then remove the data from the primary database. I know I'd need to configure a linked server in order for the data to be copied over to a different server.
Is this relatively straight forward to implement? What should I be aware of?
June 10, 2014 at 10:51 am
I wouldn't use a linked server, I'd use SSIS. I'd probably do something like this:
<ul>
<li>Load Data into archive data in current database</li>
<li>Copy data from local archive table to remote archive table</li>
<li>Delete data from current table where exists in local archive table</li>
<li>Truncate local archive table (this could be the first thing you do too)</li>
</ul>
If you implemented partitioning on the base table you could make it even faster by doing a switch to the local archive table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply