attach db file copied previously

[neo]

I got a mdf file from my co-worker
who just stopped SQL server and delete ldf file
and give me a mdf file.
if it was detached, it would work fine
but it was just copied.

Is there any way to attach this mdf file?
I tried sp_attach_db and sp_attach_singl e_file_db
and both failed.

thanks,

[Simon Hayes]

Re: attach db file copied previously

second714@hotma il.com (neo) wrote in message news:<155f8e7d. 0311241337.7338 362d@posting.go ogle.com>...[color=blue]
> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
>
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_singl e_file_db
> and both failed.
>
> thanks,[/color]

If sp_attach_singl e_file_db failed, then there is no other way to
attach the file. In future, it would be better to ask your colleague
to give you a backup of the database, not the database file(s). This
also means your colleague will not have to stop SQL Server.

Simon

[Neil Pike]

Re: attach db file copied previously

What error do you get back from the sp_attach_singl e_file_db command?
[color=blue]
> I got a mdf file from my co-worker
> who just stopped SQL server and delete ldf file
> and give me a mdf file.
> if it was detached, it would work fine
> but it was just copied.
>
> Is there any way to attach this mdf file?
> I tried sp_attach_db and sp_attach_singl e_file_db
> and both failed.
>
> thanks,
>[/color]

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see

http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps

(faqxxx.zip in lib 7)
or https://www.ntfaq.com/Articles/Index...partmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

[neo]

Re: attach db file copied previously

I ran
sp_attach_singl e_file_db 'Spectrum', 'e:\spectrum.md f'
and got

Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'd:\Spectrum\Da ta\Spectrum_log .LDF' may be incorrect.

Neil Pike <neilpike@compu serve.com> wrote in message news:<VA.000010 10.008366e0@com puserve.com>...[color=blue]
> What error do you get back from the sp_attach_singl e_file_db command?
>[color=green]
> > I got a mdf file from my co-worker
> > who just stopped SQL server and delete ldf file
> > and give me a mdf file.
> > if it was detached, it would work fine
> > but it was just copied.
> >
> > Is there any way to attach this mdf file?
> > I tried sp_attach_db and sp_attach_singl e_file_db
> > and both failed.
> >
> > thanks,
> >[/color]
>
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforu...?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or https://www.ntfaq.com/Articles/Index...partmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq[/color]

[Erland Sommarskog]

Re: attach db file copied previously

[posted and mailed, please reply in news]

neo (second714@hotm ail.com) writes:[color=blue][color=green][color=darkred]
>> > I got a mdf file from my co-worker
>> > who just stopped SQL server and delete ldf file
>> > and give me a mdf file.
>> > if it was detached, it would work fine
>> > but it was just copied.[/color][/color]
>
> I ran
> sp_attach_singl e_file_db 'Spectrum', 'e:\spectrum.md f'
> and got
>
> Could not open new database 'Spectrum'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'd:\Spectrum\Da ta\Spectrum_log .LDF' may be incorrect.[/color]

Tell your co-worker to never do that again! Deleting LDF files is a
very stupid thing to do!

It is not clear how you co-worker shut down SQL Server, but it does
not seem that this database was shut down cleanly. If this database
was in the midst of the transaction, this transaction needs to be
rolled back, to bring the database to a consistent state. But to
roll back the transaction, you need the transaction log.

Yes, there *are* ways to attach a single MDF file in such a situation,
but these ways are not documented with a very good reason. It follows
from the previous paragraph that the database you get might be grossly
corrupt. On the other hand, if there was no activity in the database
at the time of the shutdown,

If you have a backup of this database, I suggest that you restore it.
If the database contains data which is not on a backup, and you really
need to save as much as possible, I would suggest that you open a
case with Microsoft. That is not going to be cheap, but neither is it
cheap to lose the data.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp