[thelist] simple method for uploading CSV files to MSSQL

[Ken Schaefer]

Hi there,

A couple of queries about this tip.

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

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).

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?

Cheers
Ken


----- Original Message ----- 
From: "Paul Cowan" <evolt at funkwit.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, August 05, 2003 10:18 AM
Subject: RE: [thelist] simple method for uploading CSV files to MSSQL


:
: .jeff wrote:
: > give em a file upload and then do a bulk insert.
:
: Ooh, this calls for an important SQL-type tip:
:
: <tip type="MS SQL" author="Paul Cowan">
: In some circumstances, MS SQL server BULK INSERTs are non-logged
: operations[1]. Basically, this means that the details of the inserted rows
: are not kept in the transaction log, which improves performance, but
: also means that if your DB dies, and you need to go to backups and roll
: forward through the day's transaction log to get to the point just before
: the crash... well, you won't be able to.
:
: Once there's a non-logged operation in a transaction log, you can't
: restore backups of that log (in fact, you can't back it up), until
: you've done your next full DB backup. Your transaction logs are
: effectively useless, in other words.
:
: SELECT INTO does the same thing -- can bite you on the posterior in a big
: way. Be careful.
:
: [1] but not all. There are about five things[2] which determine if a bulk
: insert is logged or non-logged.
: [2] don't ask me, I don't understand them.
: </tip>
: