[thelist] simple method for uploading CSV files to MSSQL

Paul Cowan evolt at funkwit.com
Mon Aug 4 23:18:12 CDT 2003


Ken Schaefer wrote:
> a) I thought there was only 1 setting (not 5) that affected whether bulk
> operations were logged or not. Basically what recovery model you choose
> for your database:
>     - simple recovery: no operations are logged
>     - bulk-logged recovery: bulk operations (SELECT INTO, bcp, CREATE
> INDEX etc) are minimally logged
>     - full recovery: everything is logged

Damn, back into Soukup and Delaney I go.... if I'm not back in 5, send
for backup...

.... OK, back.

(Should point out that I'm talking SQL 7 here, not 2000, but I'm fairly
sure most of this still stands, I don't think it changed significantly.
Could be wrong though.)

The four things (not 5) that affect whether a given operation is logged
are:
1) If the TABLOCK hint is used in the bulk copy command (DB options
   can set this on without it being specified in each query)
2) If the "select into/bulk copy" option is set (on by default, I think)
3) If the table has indexes
4) If the table is replicated

According to Soukup & Delaney, non-logged will only be used if TABLOCK
is used, select into/bulk copy is enabled, the table has no indexes,
and it's not replicated.


> b) Even if you have using bulk-logged recovery you can still backup your
> transaction logs. What you can't do it point-in-time recovery. You can
> either apply the entire transaction log backup, or not apply the log at
> all (i.e. it's an all or nothing type thing).

Again, SQL 7.0 Soukup/Delaney, but:
	Note that if you use SELECT INTO with a permanent table,
	your next backup must be a full database backup because the
	transaction log won't have the records for these operations.
	In fact, you'll get an error message if you try to back up the
	transaction log after running an unlogged operation.

Elsewhere, re UPDATETEXT/WRITETEXT (also non-logged):
	in the face of a failure after a nonlogged operation, your
	database is only as good as your last full or differential
	backup and the transaction dumps up to the issuance of the
	nonlogged operation. You can't do further transaction dumps
	after a nonlogged operation is performed.

Pretty sure the first point is right, because whenever I do a 'copy
all objects' DTS job into our (dev!) database, our DBA gets stroppy because
transaction logs won't back up. Note that the 'copy all objects' job
turns the select into/bulkcopy option on if it isn't on, which is frankly
kind of evil.

> Now, I'm not 100% sure about (b) since I don't usually run in bulk-logged
> recovery mode, however the idea that you can't back up your transaction
> logs if you have a bulk insert operation flies in the face of everything
> I've experienced/know about SQL Server. I'd be very concerned if what you
> say is correct. Do you have further information?

As I say, it may have changed in SQL 2000 (that'll teach me to work for
a company that's too cheap to upgrade), but a quick look at the SQL2000
BOL seems to imply that the rules are largely the same.

Like I say, one to watch out for... hence the tip.

Paul


More information about the thelist mailing list