HELP! Lost LDF file, multiple MDF files, no backups!

I am desperate...
I have a database (I didn't build this one...) where there are 4 MDF
files, and in trying to clear out the two LDF files, which where
initially set to autogrow and were larger than the actual database,
and backup of the transaction log didn't shrink it, I detached the
database, deleted the LDF files, and tried to reattach, which, when
there is a single MDF file, it works!! I cannot reattach this
database!!
Here is the error:
Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
aborted.
Device activation error. The physical file name 'd:\yadayada_01.ldf'
may be incorrect.
Device activation error. The physical file name 'd:\yadayada_02.ldf'
may be incorrect.
I've tried everything I can think of... trying to figure this out
before someone notices (its not a very often used database).
Please help...
Thanks,
CalgaryDataGrlHello,
Try using the Create Database with Attach_Rebuild_Log option. See the below
eg:-
USE [master]
GO
CREATE DATABASE [Test] ON
(FILENAME = N'D:\Data\Test_data.mdf')
FOR ATTACH_REBUILD_LOG
GO
Thanks
Hari
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171323042.569709.53480@.l53g2000cwa.googlegroups.com...
>I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
>|||On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> Try using the Create Database with Attach_Rebuild_Log option. See the bel
ow
> eg:-
> USE [master]
> GO
> CREATE DATABASE [Test] ON
> (FILENAME = N'D:\Data\Test_data.mdf')
> FOR ATTACH_REBUILD_LOG
> GO
> Thanks
> Hari
>
Hi Hari,
I'm getting an error:
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ATTACH_REBUILD_LOG'.
Any thoughts?
Stacy|||Are you on SQL Server 2005?
You should probably contact product support to help walk you through this
correctly - and you should get yourself a decent backup strategy. Send me
mail through the blog link below if you want help with this.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"CalgaryDataGrl" <calgarydatagrl@.gmail.com> wrote in message
news:1171556088.308708.76810@.p10g2000cwp.googlegroups.com...
> On Feb 12, 6:09 pm, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hi Hari,
> I'm getting an error:
>
> Server: Msg 102, Level 15, State 1, Line 3
> Incorrect syntax near 'ATTACH_REBUILD_LOG'.
> Any thoughts?
> Stacy
>|||On 12 Feb, 23:30, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
Please don't delete log files. That's a great way to lose data.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Feb 13, 5:30 am, "CalgaryDataGrl" <calgarydata...@.gmail.com> wrote:
> I am desperate...
> I have a database (I didn't build this one...) where there are 4 MDF
> files, and in trying to clear out the two LDF files, which where
> initially set to autogrow and were larger than the actual database,
> and backup of the transaction log didn't shrink it, I detached the
> database, deleted the LDF files, and tried to reattach, which, when
> there is a single MDF file, it works!! I cannot reattach this
> database!!
> Here is the error:
> Error 1813: Could not open new database 'dbname'. CREATE DATABASE is
> aborted.
> Device activation error. The physical file name 'd:\yadayada_01.ldf'
> may be incorrect.
> Device activation error. The physical file name 'd:\yadayada_02.ldf'
> may be incorrect.
> I've tried everything I can think of... trying to figure this out
> before someone notices (its not a very often used database).
> Please help...
> Thanks,
> CalgaryDataGrl
You should have kept a backup (copy the files into a separate
directory) before making drastic changes so that you can revert to
original state in case things just work out the way you wanted (in
this situation).