SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hardik Doshi / Create Database / Latest Posts

RE: Create Database

manik123 — Tue, 29 Jan 2013 08:12:14 GMT

nice question.... new thing is added in my knowledgemanik*******

RE: Create Database

kapil_kk — Sat, 06 Oct 2012 07:29:50 GMT

+1 :-)

RE: Create Database

Neha05 — Wed, 08 Aug 2012 12:19:21 GMT

Got it right.

RE: Create Database

nerosdk71 — Tue, 31 Jul 2012 22:34:56 GMT

Why don't you remove a question or clarify it when it is clearly wrong and both answers are working.I have tested it on my SQL Server and the "wrong" option works fine.Soren

RE: Create Database

L' Eomot Inversé — Wed, 18 Jul 2012 11:24:53 GMT

Like those who commented earlier, I think both options work (provided there was a clean shutdown). I picked the second because although I knew the first optioopn would also work I guessed that the question's author didn't.

RE: Create Database

EL Jerry — Fri, 22 Jun 2012 08:50:33 GMT

Thanks for the question.I agree that both options may be correct, depending if the database was shut down cleanly AND only one log file existed for the database file. BOL specifies:- "If multiple log files exist, they must all be available." and- "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 the FOR_ATTACH option."El" Jerry.

RE: Create Database

kapfundestanley — Fri, 22 Jun 2012 01:46:33 GMT

A simple one for me thank you very much.

RE: Create Database

Koen Verbeeck — Thu, 21 Jun 2012 14:20:28 GMT

Pretty easy if you had to attach the AdventureWorks MDF files multiple times :-D

RE: Create Database

gchornenkyy — Thu, 21 Jun 2012 14:11:18 GMT

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.

RE: Create Database

GilaMonster — Thu, 21 Jun 2012 11:14:22 GMT

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.

RE: Create Database

sestell1 — Thu, 21 Jun 2012 09:38:23 GMT

[quote][b]SQLRNNR (6/21/2012)[/b][hr][quote][b]Thomas Abraham (6/21/2012)[/b][hr][quote][b]tommyh (6/21/2012)[/b]Works just fine to attach a database with just "for attach" with only one log file. /T[/quote]I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort![/quote]I have used the For Attach method several times as well.[/quote]I've done this as well without the log file for various reasons.

RE: Create Database

OzYbOi d(-_-)b — Thu, 21 Jun 2012 08:29:29 GMT

Answered off the cuff believing that FOR ATTACH rebuilds the log. Thanks tommyh for confirming... Missed the earlier postCheers

RE: Create Database

SQLRNNR — Thu, 21 Jun 2012 07:51:46 GMT

[quote][b]Thomas Abraham (6/21/2012)[/b][hr][quote][b]tommyh (6/21/2012)[/b]Works just fine to attach a database with just "for attach" with only one log file. /T[/quote]I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort![/quote]I have used the For Attach method several times as well.

RE: Create Database

Thomas Abraham — Thu, 21 Jun 2012 07:25:47 GMT

[quote][b]tommyh (6/21/2012)[/b]Works just fine to attach a database with just "for attach" with only one log file. /T[/quote]I figured it would. But it's nice to have it confirmed. (Yes, I don't always trust BOL.) Thanks for the effort!

RE: Create Database

tommyh — Thu, 21 Jun 2012 07:03:09 GMT

[quote][b]Thomas Abraham (6/21/2012)[/b][hr][quote][b]David P Fisher (6/21/2012)[/b][hr][quote][b]ipounder (6/21/2012)[/b][hr]Seems to me that both answers can be correct.Ian[/quote]I agree :-)[/quote]Anybody care to test it? :-)Thanks for the question.BTW, love the cat with the mice crawling over it.[/quote]Works just fine to attach a database with just "for attach" with only one log file. /T

RE: Create Database

Thomas Abraham — Thu, 21 Jun 2012 06:23:11 GMT

[quote][b]David P Fisher (6/21/2012)[/b][hr][quote][b]ipounder (6/21/2012)[/b][hr]Seems to me that both answers can be correct.Ian[/quote]I agree :-)[/quote]Anybody care to test it? :-)Thanks for the question.BTW, love the cat with the mice crawling over it.

RE: Create Database

David P Fisher — Thu, 21 Jun 2012 03:27:19 GMT

[quote][b]ipounder (6/21/2012)[/b][hr]Seems to me that both answers can be correct.Ian[/quote]I agree :-)

RE: Create Database

Jakub.Janda — Thu, 21 Jun 2012 03:15:14 GMT

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

RE: Create Database

call.copse — Thu, 21 Jun 2012 03:03:24 GMT

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

RE: Create Database

Toreador — Thu, 21 Jun 2012 03:02:20 GMT

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.

RE: Create Database

ipounder — Thu, 21 Jun 2012 03:01:30 GMT

Seems to me that both answers can be correct.Ian

RE: Create Database

440692 I am just a number — Thu, 21 Jun 2012 01:00:16 GMT

Am I reading this right?FOR ATTACH[quote]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. [/quote]Given the assumtions in the question, I think both answers are correct.

Create Database

Hardy21 — Wed, 20 Jun 2012 22:30:01 GMT

Comments posted to this topic are about the item [B]Create Database[/B]