Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Attaching .MDF File

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-19 : 08:22:47
James writes "Hi All
I have recently installed VB.NET and this blew up my SQL Server.
I copied all my .MDF .LDF database files to Dataold dir and reinstalled my SQL Server.
Everything worked ok except for one database
I used this

EXEC sp_attach_single_file_db @dbname = 'AMPSLindenCpac',
@physname = 'c:\DataFormssql7\AMPSLindenCPAC_Data.MDF'

And keep getting the error

Database 'AMPSLINDENCpac'
cannot be opened because some of the files could not be activated.

I have read similar articles on this but everyone says the database is fried.

Is there no way to repair or restore etc. the .MDF file

I have had this problem before, normally if I take a database from a site which has a K:\ drive and try to install it on my C:\Drive I get this error.
My fix is to backup the database then restore it to the Site C:\Drive then I back it up again to a .BAK File and then can copy this to my local drive and use the above attach single file.
Any help on this would be great.
Cheers
James Anthony"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-19 : 12:29:38
Yes the MDF file is bad. Always perform full backups when you make changes to the database server. An MDF file is usually only available for an attach if the MSSQLSERVER service was stopped at that time or if a detach was performed. I have had success once with the MDF file being able to be attached without either of these two conditions be true, but I was very surprised.

You installed VB.NET on the SQL Server box? Not recommended, but I guess you realize that now.

Tara
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2003-11-19 : 13:02:03
If you have the LDF, why are you doing an attach_single_file_db? Why not just sp_attach_db?
I'm not sure it will make any difference in this case, but it's worth a try.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

sbt1
Yak Posting Veteran

89 Posts

Posted - 2003-11-19 : 15:53:02
I've seen this error but only if I try to attach a single file DB without having the .LDF file. Are you sure the .LDF is named the same as the .MDF?

I frequently copy my database onto a flash drive and install on my home PC for testing. I do this by stopping the SQL Server service, copying the .MDF and .LDF files to the flash drive, then restarting the SQL Server service.

On the home PC I stop the service, copy those 2 files to my \data folder, restart the service, then attach the database.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-19 : 15:56:00
You do not need an LDF for the sp_attach_single_file_db stored procedure. As a matter of fact, if you have the LDF, just delete it in case that file is the problem.

You need both MDF and LDF for sp_attach_db.

Tara
Go to Top of Page

jimmyread
Starting Member

1 Post

Posted - 2009-08-04 : 06:00:45
unspammed
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-02-07 : 17:18:50
Sometimes, when you detach a database the ownership is changed to the person who detached the database. This can prevent attaching the database.

Check the file ownership and also check the read only flag. Reset the ownership to your SQL Server service account or to the administrators group (if running as local system).

Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-07 : 17:19:26
It's a post from 7 years ago. Only looks recent because somebody spammed it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -