changing the subject? change the subject line! (was RE: [thelist] simple method for uploading CSV files to MSSQL)

[Joel D Canfield]

Aw, c'mon, people---if you're going off on a tangent, however useful and
valid, how's about changing the subject so I don't think I'm getting
dozens of replies to my original question?
 
spinhead

	-----Original Message----- 
	From: Ken Schaefer 
	Sent: Mon 8/4/2003 9:39 PM 
	To: thelist at lists.evolt.org 
	Cc: 
	Subject: Re: [thelist] simple method for uploading CSV files to
MSSQL
	
	


	----- 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
	
	--
	* * Please support the community that supports you.  * *
	http://evolt.org/help_support_evolt/
	
	For unsubscribe and other options, including the Tip Harvester
	and archives of thelist go to: http://lists.evolt.org
	Workers of the Web, evolt !