Backup large Table

Question

Post reply

Backup large Table

Minnu

SSCertifiable

Points: 6451
More actions
May 24, 2013 at 3:15 am

#277810

Hi Team,
I've a table with 75+ columns and 70000 records, i want to have a backup .sql file.
am trying using SSMS > Rt click Database > Tasks > Generate Scripts > ...
.sql file is created but while opening the same in SSMS getting below error.
Error HRESULT E_FAIL has been returned from a call to a COM component
Please help me.....

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

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

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

What is the size of your script file?

Looks like you're having this problem:-

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/51ef4b81-6677-4e2d-8b80-648633cf699e

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Hi,

370 MB.

is there any way to generate the .sql file.

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 3

Well it depends on exactly what you're trying to do. I would suggest creating a script for the schema of the table, but perhaps export the data to a file to reimport it if you need it. Your table will also be backed up in any database backups you do.

Why exactly do you want a backup of the table only? If it's purely for a temporary change then you could also create a new table to temporarily store the data.

The method you're choosing will perform a single insert for each row of data, hence why your script is so big.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

Minnu (5/24/2013)
Hi Team,
I've a table with 75+ columns and 70000 records, i want to have a backup .sql file.
am trying using SSMS > Rt click Database > Tasks > Generate Scripts > ...
.sql file is created but while opening the same in SSMS getting below error.
Error HRESULT E_FAIL has been returned from a call to a COM component
Please help me.....

I'd recommend NOT using "Generate Scripts" for generating 70,000 rows of data. It'll create 70,000 INSERT/VALUE rows and it'll take a month of Sundays to execute.

My recommendation would be to use BCP "out" to build either a native SQL export or a tab delimited export. It'll run in seconds either for an export or an import and the code could be scheduled as a job with a CmdExec task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)