What is the BEST way to take a back up of Table

Question

What is the BEST way to take a back up of Table

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
January 5, 2010 at 5:56 am

#137179

Hi,
I am designing one SSIS package where in the last task, I would need to copy the content of one huge table to another table (kind of a back up copy). I am confused with the below 3 options by considering the maintenance & performance issues.
Option1:
One "Execute SQL task" to truncate the Backup table.
One Data Flow task (with OLEDB source & OLEDB Destination) to copy the table data into back up table.
Option2:
One Execute Sql task with the Insert into...select.. to populate the backup table.
Option3:
Use Bulk Copy option though I am NOT familiar with this.
Please let me know the best option to implement in SSIS package. Also, please suggest me if there are any other best options.
Thanks all,
Suresh
Regards,
Suresh Arumugam

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think the fastest way will be to Truncate then do Bulk Insert. The slowest would be TRUNCATE then data flow task as the data flow task does the move row by row.

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

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

Thanks Jack.

Do you mean that I should use "Insert into.. select.." option to populate the backup table or should use "Bulk Copy option"?

Regards,

Suresh

Regards,
Suresh Arumugam

c00ler01 Ten Centuries Points: 1180 More actions · Answer 3

Store table in a seperate file group and take a backup of that file group?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I mean try both options and see which one works best. I just don't think the DataFlow task will work well because it is RBAR (although in memory) and you aren't doing any processing, just copying.

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