[thelist] simple method for uploading CSV files to MSSQL

[Ken Schaefer]

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


: (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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I believe this has changed with SQL Server 2000. Now it's just a bunch of
flags in a system table. These then determine whether your database is
running in simple, bulk-logged or full recovery mode.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This has definately changed in 2000:

<quote>
(from Delany: Inside SQL Server 2000)

Second, you can freely switch between the FULL and BULK_LOGGED modes without
worrying about your backup scripts failing. Prior to SQL Server 2000, once
you performed a SELECT INTO or a bulk copy, you could no longer back up your
transaction log. So if you had automatic log backup scripts scheduled to run
at regular intervals, these would break and generate an error. This can no
longer happen. You can run SELECT INTO or bulk copy in any recovery mode,
and you can back up the log in either FULL or BULK_LOGGED mode.
</quote>

...and then it goes on.

In SQL Server 2000 Books Online, if you look at the "Bulk Logged Recovery"
topic you can see that you are able to back up the transaction log. However,
it does point out:

<quote>
In a Bulk-Logged Recovery model, the data loss exposure for these bulk copy
operations is greater than in the Full Recovery model. While the bulk copy
operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation basis
under the Bulk-Logged Recovery model. Under the Bulk-Logged Recovery model,
a damaged data file can result in having to redo work manually.
In addition, the Bulk-Logged Recovery model only allows the database to be
recovered to the end of a transaction log backup when the log backup
contains bulk changes. Point-in-time recovery is not supported.

</quote>


Cheers
Ken