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 !