re attach SQL database without its transaction log

I have a large database used for lab systems, one of the drives that hold the LDF failed and I cant attach the database with a new log. when I attempt the attach I get error that the database cant be rolled back, is there a way to re attach the MDF and have a new transaction log created as well as have the system not roll forward as data loss is not an issue I just need that database up.
any help is apreciated.
Regards,
AT
atorexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
0
McottuliCommented:
Did you try to create a new database which will automatically creat an .LDF file and then just replace the newly created .mdf with the good one?
0
atorexAuthor Commented:
Mcottuli,
I did that but that's where I get the error that it cant roll the data
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

McottuliCommented:
Try this

CREATE DATABASE Database1 ON
(FILENAME = N'C:\Temp\Database1.mdf')
FOR ATTACH_REBUILD_LOG
0
atorexAuthor Commented:
I get the below error when attempting the
CREATE DATABASE Database1 ON
(FILENAME = N'C:\Temp\Database1.mdf')
FOR ATTACH_REBUILD_LOG
command


File activation failure. The physical file name "S:\TM_Log_30625.LDF" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
0
McottuliCommented:
Try this one....


sp_attach_single_file_db 'Database1', 'C:\location\Database1.mdf'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atorexAuthor Commented:
this database has an MDF and an NDF how do I modify the command
0
McottuliCommented:
Not sure, I'll have to look at that, I'm assuming that you do not have a .BAK anywhere of this database?
0
atorexAuthor Commented:
no I don't, I wish I did!
0
McottuliCommented:
What kind of errors are you getting when attaching with the last script? If you haven't tried yet, Make a complete compy of the good .mdf before doing so.
0
atorexAuthor Commented:
This was the last option provided, is this what you want to see the results?
sp_attach_single_file_db 'Database1', 'C:\location\Database1.mdf'

I can not run this as the database has 2 data files one MDF and one NDF
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.