SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Attaching .MDF File
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/19/2003 :  08:22:47  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
35953 Posts

Posted - 11/19/2003 :  12:29:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
1974 Posts

Posted - 11/19/2003 :  13:02:03  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 11/19/2003 :  15:53:02  Show Profile  Reply with Quote
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

USA
35953 Posts

Posted - 11/19/2003 :  15:56:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Posts

Posted - 08/04/2009 :  06:00:45  Show Profile  Reply with Quote
unspammed
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 02/07/2011 :  17:18:50  Show Profile  Reply with Quote
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

USA
35953 Posts

Posted - 02/07/2011 :  17:19:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000