attach db with a deleted log

Hello,
How can i attach a db where there is a log deleted.
When i want to attach the db, i cannot click the ok button because a
second log-file is missing.
Anyway to work around this?Don't use Enterprise Manager. Use sp_attach_db or
sp_attach_single_file_db. See Books Online for details and examples of
those commands.
Make sure you have a good backup of your MDF/LDF files before you try
to attach them.
David Portas
SQL Server MVP
--sql

Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>

Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:

> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>
>|||You can't add an unmacthing transaction log file to a database. The purpose
of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup
that the lsn number for
the log records matches with what to expect for the database. If restore isn
't a viable option, I
suggest you let MS Support help you. They might have a way to get the databa
se back, in some shape.
And, make sure you give access to the system to people who might do these op
erations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that t
he
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode an
d
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:

> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...[vbcol=seagreen]
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>

Attach database without log file

How can I attach a database file if my log file is not available. I only have the database file because the log file was deleted. When I try attaching the database file, I get the error that it can not find the log file.

What is strange is that if I delete the log file for another database, I can still attach the database file and it creates a new log file for me.

I am using SQL Server 2000.

Thanks

AlexanderThere is a system stored proc for doing this called Sp_attach_single_file_db.
Though this will only work if the database had previously been detached cleanly. i.e. if it was detached and then the log file was lost you should be ok. If you lost the log file before detaching, your best bet is to restore a backup.|||Ronba,

Thanks fro your reply, I later discovered the same thing that you said. I was trying to attach the single file, but the database was not previously detached cleanly.

Alexander

AT the moment i have an SQL SELECT Statement as follows....

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id

order by date DESC

is it possible to join to another table in the same query to get a value out.

it would be JOIN categories C on H.CategoryID = C.CategoryID

is this possible. can anyone help?

This should help

http://www.vb-tips.com/InnerJoin.aspx

|||

is this what you want?

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id JOIN categores C on h.CategoryID=C.CategoryID

order by H.date DESC

|||

Yes, Thanks for help.