Create Database

  • Comments posted to this topic are about the item Create Database

    Thanks

  • 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.

  • Seems to me that both answers can be correct.

    Ian

  • 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.

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

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

  • 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 🙂

  • 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

  • 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

  • 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

  • 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

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

    Thanks tommyh for confirming... Missed the earlier post

    Cheers

  • 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.

  • 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
  • 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