469,939 Members | 2,441 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,939 developers. It's quick & easy.

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_single_file_db
and both failed.

thanks,
Jul 20 '05 #1
4 7168
se*******@hotmail.com (neo) wrote in message news:<15**************************@posting.google. com>...
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_single_file_db
and both failed.

thanks,


If sp_attach_single_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
Jul 20 '05 #2
What error do you get back from the sp_attach_single_file_db command?
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_single_file_db
and both failed.

thanks,


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 http://www.ntfaq.com/Articles/Index....partmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Jul 20 '05 #3
neo
I ran
sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
and got

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

Neil Pike <ne******@compuserve.com> wrote in message news:<VA******************@compuserve.com>...
What error do you get back from the sp_attach_single_file_db command?
> 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_single_file_db
and both failed.

thanks,


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 http://www.ntfaq.com/Articles/Index....partmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Jul 20 '05 #4
[posted and mailed, please reply in news]

neo (se*******@hotmail.com) writes:
> 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.


I ran
sp_attach_single_file_db 'Spectrum', 'e:\spectrum.mdf'
and got

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


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Stephen Witter | last post: by
68 posts views Thread by Martin Joergensen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.