Create Database

Question

Post reply

Create Database

Hardy21

SSCrazy Eights

Points: 9708
More actions
June 20, 2012 at 10:30 pm

#260366

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

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

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

Ian_McCann SSCertifiable Points: 5163 More actions · Answer 1

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 More actions · Answer 2

ipounder

SSCommitted

Points: 1541

June 21, 2012 at 3:01 am

#1503819

Seems to me that both answers can be correct.

Ian

Toreador SSChampion Points: 11365 More actions · Answer 3

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: 18048 More actions · Answer 4

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

Jakub.Janda Default port Points: 1424 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 6302 More actions · Answer 8

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 More actions · Answer 9

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: 281334 More actions · Answer 10

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 SSCarpal Tunnel Points: 4054 More actions · Answer 11

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

Thanks tommyh for confirming... Missed the earlier post

Cheers

sestell1 SSChampion Points: 10230 More actions · Answer 12

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: 1004504 More actions · Answer 13

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: 687 More actions · Answer 14

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.