Well no, not really. The question was can you prevent it logging calls on Updates etc - the simple answer is a stone walled no, there are no ifs or buts here - you cannot do it. To do so would defeat the purpose of using a database and maintaining its integrity.
The backup approach is simply saying only bring back what you filter - ALL of the updates/logs are still within it you just do not restore them. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 26 July 2006 15:09 To: SQL Subject: RE: Update without logging Under Microsoft SQL Server 2000, saying it *cannot* be done is only partially true. You can alter the recovery model for the database on the fly using ALTER DATABASE commands, but as you mentioned, it really messes with your backup integrity. You would want to backup the database right after you finish, because any transaction log backups made prior to this just became useless. SIMPLE recovery truncates the transaction log as soon as each batch is fully committed, where FULL recovery is what most people use. Obviously, you would want to gather up as many updates as possible to process at one time so you don't have to constantly switch your recovery model and perform a full backup. ALTER DATABASE db1 SET RECOVERY SIMPLE GO UPDATE table1 SET column1 = 'lots of unlogged data' GO ALTER DATABASE db1 SET RECOVERY FULL GO Thanks, -- Eric _____________________ "For all its flaws, I would feel safer to have my children grow up in a world dominated by the United States than by any other country." -- Kobsak Chutikul (deputy leader in Thailand), AP news release 3/7/2003 "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> wrote on 07/26/2006 07:01:24 AM: > Cannot be done, certainly not in 2000 (and for sure 7). If you could do > this, you would effectively be rendering backups and data integrity useless. > > There are ways you can cut down the amount of info logged but using SELECT > INTO, BCP. > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 26 July 2006 02:48 > To: SQL > Subject: Update without logging > > I'm trying to clean up a massive logging table and I'm using update > statements in my work. It's all going ok, but the tempDB and log for the > table is going crazy. Is there any way to do an update without it being > logged or without the tempdb filling to a few hundred meg? > Thanks > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2530 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
