Difficulty exporting table

Question

Difficulty exporting table

Erin Ramsay

SSCrazy Eights

Points: 9728
More actions
October 12, 2010 at 1:05 pm

#230332

I need some advice on exporting a large table.
I've tried exporting from one server to another, exporting to a flat file, etc. and I need help.
I have a table with 286 million rows that needs to come off a 24/7 production server and be migrated to an archive database on another server.
Problem is that I can't take the production server offline and it's taking FOREVER trying to export data to either a file or another server and it's slowing down the production database.
Does anyone have any ideas on how migrate this table to it's new location quicker perhaps?
Don

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

You have a few options.

If you're using enterprise, you could take a database snapshot and then read from the snapshot without blocking production.

You could also replicate that table to another server, and then pause replication when you want to export it to a flat table.

You could also take a full backup of the database, restore it, and then read the table from that.

Or you could use (nolock) and get a bunch of dirty data most likely..but I'd consider that a last resort.

edit: didn't mean to press submit yet. I was going to say I had a similar issue where I needed to archive a table that was roughly 2 billion rows and growing by about 100 million per month. I set up snapshot replication to run once a week to copy it to the archive database.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

How are you doing the export?

Have you tried doing this in batches instead of the whole table at once?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

Derrick,

Unfortunately, we've grown out of control on the production database (happened before I came on board) and we can't replicate it due to storage issues.

Alvin,

How do you export in batches? I tried to use the Export Wizard but it doesn't seem to give me the option of batches. If I could do it 1 million rows at a time that would help.

To clarify, this table is no longer being used at all. Nothing depends on it or vice versa. It's just data that I want to keep for auditing purposes.

Don

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Donalith (10/12/2010)
Derrick,
Unfortunately, we've grown out of control on the production database (happened before I came on board) and we can't replicate it due to storage issues.
Alvin,
How do you export in batches? I tried to use the Export Wizard but it doesn't seem to give me the option of batches. If I could do it 1 million rows at a time that would help.
To clarify, this table is no longer being used at all. Nothing depends on it or vice versa. It's just data that I want to keep for auditing purposes.
Don

Filter it by date or identity column and do it in groups..i.e. one year at a time, 10 million IDs at a time, or however it's stored sequentially (usually your PK/clustered index)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Donalith (10/12/2010)
Derrick,
Unfortunately, we've grown out of control on the production database (happened before I came on board) and we can't replicate it due to storage issues.
Alvin,
How do you export in batches? I tried to use the Export Wizard but it doesn't seem to give me the option of batches. If I could do it 1 million rows at a time that would help.
To clarify, this table is no longer being used at all. Nothing depends on it or vice versa. It's just data that I want to keep for auditing purposes.
Don

Use a where clause in your data selection to export in batches. I suspect that if you have a clustered index on the table, the column(s) that make up that index might be the best to use for the where clause.

... and ... whatever Derrick said. He's thinking along the same lines as I am.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

Thanks all. What I gather from the responses is that there really isn't a faster way to move/copy the table.

For example, in MySQL I could just go into the operating system if necessary and move the table file itself. No chance of that here huh?

Don

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

Donalith (10/12/2010)
Thanks all. What I gather from the responses is that there really isn't a faster way to move/copy the table.
For example, in MySQL I could just go into the operating system if necessary and move the table file itself. No chance of that here huh?
Don

Nope..

If the table is on its own filegroup, you could just do a filegroup backup too...just throwing out ideas here. One of the methods in this thread has to suit your needs.