Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Copy table in a database without transaction logging.

    Hi y'all, I am doing some searching in the archived threads, but I have a need to copy a table in a database to a new table in the same database, but the new table will be just a table with test data. There are several million rows in the table and I want to do the copy without logging the new inserts in the transaction log.

    Is there an easy way to do this? I found this in my search efforts so far, but am just wondering if there is an easier/better way to accomplish what I want to do.

    BTW, I normally wouldn't care, but the boss is complaining that it is taking too long to do the copy for a different team, so asked if I knew a way to copy data to a new table without logging. I don't, so here I am

    Here is what I found so far:
    Following 3 things need to be done
    1) create table as not logged initially
    2) set autocommit=off
    3) and activate the not logged initially option
    Now the inserts happen without the use of transaction logs
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    bcp out

    SET RECOVERY TO BULK LOGGED

    bcp in

    put recovery back to FULL
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks Sean. I see your instruction uses BCP OUT and IN to move the data, which I (or rather, the boss) was hoping to avoid. He just wants to copy from one table to a test table in the same database. Looking at BOL though, the recovery mode setting looks like it will allow an INSERT INTO with minimal logging
    Quote Originally Posted by BOL
    The Bulk-Logged Recovery model provides protection against media failure combined with the best performance and minimal log space usage for certain large-scale or bulk copy operations. These operations are minimally logged:

    SELECT INTO.


    Bulk load operations (bcp and BULK INSERT).


    CREATE INDEX (including indexed views).


    text and image operations (WRITETEXT and UPDATETEXT).
    so that might work here too. I guess BULK LOGGED is as close as he can get to unlogged, huh? I'll poke around in BOL for details, but I'm not sure what "minimally logged" actually means. I think they should allow us to NOT log, with a disclaimer maybe popping up at execution saying "are you sure?, you are about to do something really stupid!" or some similar easy-to-ignore message.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not think that covers INSERT INTO. It does cover SELECT INTO as you note above.

    Minimal logged pretty much means it will log something happened, but the T-log will not have all of the details in case you attempt a point in time recovery with a T-log backup. So you can not recover those transactions until you do your next full backup.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * INTO dbo.dbname.table FROM table

    Are you serious?

    Same server?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do not get snarky with me. that's what BOL says. See the qoute in the cowboys post.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yep, serious as a hemophiliac in a razor blade factory.

    Same server, same database. He just wants to copy a VERY large table and not have it slowed down by the transaction log inserts...any other ideas besides those covered so far? I'm afraid I'll have to get involved with DB setting changes like these, since "they" aren't well versed in much besides random upper-level work. *cringe*
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Thumbs up

    Quote Originally Posted by TallCowboy0614
    Yep, serious as a hemophiliac in a razor blade factory.

    Hahahahah so nice! Where does it comes from ?


    Cheers to that!
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Why don't you juste make a copy of the db so the copied db is a full test only enviroment?

    May take a little longer to copy but at least you're not gonna mess up the data from the others table.

    And your transaction log will be affected only in your test environement ?


    although I did some googleling and almost 90% of the search results were telling to use bulk to do that....
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks O...and thanks for the idea, but we already have test environments and databases. This is just for a one-off project. I tell ya, by now they could have done the update and just let it go and use the translog. For crissake, the DB is backed up and shrunk every night automagically anyway, so it's only a temporary issue to have a large log....errr...file.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by TallCowboy0614
    For crissake, the DB is backed up and shrunk every night
    Paul - I am shocked

    Third party BU stuff tends to offer table level restores now. Not tried them (we either need to restore the lot or nothing) but if you use litespeed, redgate etc this might be an option.

    Or just log it you big p*$$y
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    It's not me...dang it. I'm no p*$$y...it's the boss, who doesn't want to wait for the task to finish with logging on...I tell ya, as long as this has dragged on, they'd be done by now if they had just sucked it up and did it.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •