September 22, 2007 at 8:44 pm
I recently had a HD crash on me and have my MDF files, but no LDFs. After repaving my server, I have an updated/patched SQL Server 2005 box, but no DBs on it. I can't seem to find any instrucstions for how to load in my dbs with just the MDFs. Please help?
I've tried to restore, I've tried attaching, nothing is working. Please don't tell me I'm SOL?!
Thanks.
September 22, 2007 at 8:57 pm
You should be able to attach them. SQL should be able to recreate the log files. Do you not have any db backps?
I hope this is not a production system you're talking about.
What error do you get when you try to attach the db?
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
September 22, 2007 at 9:08 pm
AHA! I think I got it! After you create the DB, you goto Tasks, Import. I did that before, but I tried to use the a few of the Data Source options, but this time the one that worked for me was the .NET Framework Data Provider for SqlServer making sure to get data into the Source options, specifically the AttachDbFilename, and Securty's User ID and Password. After all the prompts, my data was brought in! Beautiful! :>
September 22, 2007 at 9:32 pm
No they are not production databases. :> It's my own dbs for learning with, but I do have one with quite a bit of data I would hate to lose (that one has backups :>).
The error I get is
Attach database failed for Server 'MyServer'. (Microsoft.SqlServer.Smo)
Additional Information
An exception occurred whle executing a Transact-SQL statement or batch.
(Microsoft.Sql Server.ConnectionInfo)
Directory lookup for the file "x:\dir\DbName_log.ldf" faild wth the operating system error 2(The system cannot find the file specified.). (Microsoft SQL Server, Error: 5133)
When I googled that error, it had some links about securty, etc. But of course that file is missing, I don't have it. :>>>>
September 22, 2007 at 9:50 pm
Ch[font=Courier New]eck whether SQL service account has modify privilege on the particular file and the folder that it exists.[/font]
Regards..Vidhya Sagar
SQL-Articles
September 22, 2007 at 10:32 pm
I'm running as the machine's administrator and the sa on the db. I think I have the right privileges? Or is there something funky with copying files across machines that screws that up?
PS Thank you to everyone helping me out! :>
September 23, 2007 at 8:42 am
Look in books online for sp_attach_single_file_db. That should do the trick.
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
September 23, 2007 at 12:55 pm
Thank you very much, that worked like a charm! It even appears I have my stored procs which didn't seem to be brought in when I did my Import trick, but when I tried to create them manually, I got errors saying they were already there. Then when I tried to execute them, I got security errors. What could that be about?
Thanks again for your help.
///Edit
PS When I did this, the log file was created in the same place as the mdf and I woud like to move it to another drive (separate drive altogether) and the sp_detach_db and sp_attach_db helped me with that (of course moving the file in between those two operations :>). Thank you again for your help! :>
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply