[thelist] simple method for uploading CSV files to MSSQL

Paul Cowan evolt at funkwit.com
Mon Aug 4 23:57:06 CDT 2003


Ken Schaefer wrote:
> 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.

According to the BOL, a minimally-logged bulk copy still requires (as
well as the recovery model, which I gather replaces the 'select into/
bulkcopy' option), the non-replication, non-trigger, non-indexed,
and TABLOCK-hinted conditions to be met.

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

Well, that's a bit nicer, but still...

OK. So we'll amend the tip to say that under certain circumstances,
bulk-copy operations can still be non-logged (or minimally-logged). If
they're non-logged (the previously-stated conditions in SQL 7; same,
but with SIMPLE recovery mode in 2000), you can't backup your transaction
logs after such an operation.

If you do minimally logged (same conditions, with BULK_LOGGED, SQL 2000
only), you can backup your trans. logs, but you restore only until the
end of your backup log, you can't do point-in-time.

If your ops are fully logged (no operations which meet these conditions
are done, or you have FULL logging mode in SQL2000), then it's operations
as per normal.

I think the main point of my tip still stands -- be careful, if you
have the wrong options bulk copies can screw you over, no matter
what version of SQL -- but I am willing to concede that I
oversimplified somewhat. :)

Cheers,

Paul


More information about the thelist mailing list