x

DTS Export execution length/advice

I need to analysis a customer's sql server database, I don't have sql server myself. It's v8, pre SSIS, so I an looking at a dts export to excel, straight forward using the wizard.

The have a lot of tables so I am hoping to export them all as one excel file. The database shows in enterprise manager as 366mb. They access their database 24 7 so I am wary about locking/crashing it and don't know if that would be an issue.

Does anyone have an idea how long the export might take to run or any other observation/recommendations.

Thanks in advance,

Tom.

more ▼

asked Dec 14 '09 at 02:06 PM in Default

boardtc gravatar image

boardtc
15 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

How about take a Full backup, restore to a new, temporary, database and then any exports you do from there will be from a consistent moment-in-time and won't interfere with their live database.

Assuming that they have enough disk space ...

... first job after Restoring might be to SHRINK the new temporary database, that would release any unused space.

... or you could just take the backup file off-site and restore on your SQL box and export etc. from there (assuming they are happy for the DB to go off-site - given that they are presumably happy for the XLS to go off site I reckon its as broad as it is long :) )

more ▼

answered Dec 14 '09 at 02:13 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

Thanks. answer implies locking, etc might be a problem, glad I did not run it! have remote access only. I did a backup and have a file 163MB, which I see in explorer. I do a refresh in enterprise manager and do not see the backup database so I did a restore to a new name and shrunk it to 232mb and exported all 84 tables in a minute!

failed to copy 2 tables with these errors:
http://i49.tinypic.com/ndkjl4.jpg
http://i49.tinypic.com/ndkjl4.jpg
Dec 14 '09 at 02:42 PM boardtc
Looks like the version of Excel you are using has a limit of 65,535 rows. The newer version of Excel allows more rows than that (but I personally wouldn't use it for "database data export" as it mucks about with the data - formatting anything that "looks like a date" as a date, converting to numeric anything that looks like a number - and chopping off any leading zeros of anything that looks like a number, and so on.
Dec 14 '09 at 04:26 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x109
x65
x27

asked: Dec 14 '09 at 02:06 PM

Seen: 849 times

Last Updated: Dec 14 '09 at 02:06 PM