Create Database

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Comments posted to this topic are about the item Create Database

    Thanks

  • Ian_McCann

    SSCertifiable

    Points: 5132

    Am I reading this right?

    FOR ATTACH

    FOR ATTACH [ WITH < attach_database_option > ]

    Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files.

    FOR ATTACH requires the following:

    All data files (MDF and NDF) must be available.

    If multiple log files exist, they must all be available.

    If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.

    Given the assumtions in the question, I think both answers are correct.

  • ipounder

    SSCommitted

    Points: 1541

    Seems to me that both answers can be correct.

    Ian

  • Toreador

    SSChampion

    Points: 11256

    Got it right, seemed an easy guess 🙂

    But when I then did a bit of research, it appears that FOR ATTACH will also work if the database had a single log file.

  • call.copse

    SSCoach

    Points: 17181

    I did divine the correct answer, however I would agree with 'Just a number' that both options would work.

  • Jakub.Janda

    Ten Centuries

    Points: 1352

    Ok, I got it wrong after reading the books online specifically on that subject. I agree, with single log file, both answers are correct.

  • David P Fisher

    SSC Eights!

    Points: 818

    ipounder (6/21/2012)


    Seems to me that both answers can be correct.

    Ian

    I agree 🙂

    The impossible can be done at once, miracles may take a little longer 🙂

  • Thomas Abraham

    SSChampion

    Points: 10761

    David P Fisher (6/21/2012)


    ipounder (6/21/2012)


    Seems to me that both answers can be correct.

    Ian

    I agree 🙂

    Anybody care to test it? 🙂

    Thanks for the question.

    BTW, love the cat with the mice crawling over it.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • tommyh

    SSCertifiable

    Points: 6252

    Thomas Abraham (6/21/2012)


    David P Fisher (6/21/2012)


    ipounder (6/21/2012)


    Seems to me that both answers can be correct.

    Ian

    I agree 🙂

    Anybody care to test it? 🙂

    Thanks for the question.

    BTW, love the cat with the mice crawling over it.

    Works just fine to attach a database with just "for attach" with only one log file.

    /T

  • Thomas Abraham

    SSChampion

    Points: 10761

    tommyh (6/21/2012)

    Works just fine to attach a database with just "for attach" with only one log file.

    /T

    I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thomas Abraham (6/21/2012)


    tommyh (6/21/2012)

    Works just fine to attach a database with just "for attach" with only one log file.

    /T

    I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort!

    I have used the For Attach method several times as well.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3994

    Answered off the cuff believing that FOR ATTACH rebuilds the log.

    Thanks tommyh for confirming... Missed the earlier post

    Cheers

  • sestell1

    SSChampion

    Points: 10230

    SQLRNNR (6/21/2012)


    Thomas Abraham (6/21/2012)


    tommyh (6/21/2012)

    Works just fine to attach a database with just "for attach" with only one log file.

    /T

    I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort!

    I have used the For Attach method several times as well.

    I've done this as well without the log file for various reasons.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Worth noting that the attach will only succeed if the database was cleanly shut down before the log was deleted. Otherwise the attach (even with Attach_rebuild_log) will fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gchornenkyy

    Say Hey Kid

    Points: 692

    BOL citation:

    FOR ATTACH [ WITH < attach_database_option > ]

    Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files.

    FOR ATTACH requires the following:

    All data files (MDF and NDF) must be available.

    If multiple log files exist, they must all be available.

    [highlight=#ffff11] If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file. [/highlight]In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database with a log that is unavailable, you must provide the log files, or the files in the FOR ATTACH clause.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply