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.
Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2003-11-19 : 08:22:47
|
James writes "Hi AllI 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 databaseI used thisEXEC 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 fileI 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.CheersJames 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 |
|
|
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.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
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. |
|
|
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 |
|
|
jimmyread
Starting Member
1 Post |
Posted - 2009-08-04 : 06:00:45
|
unspammed |
|
|
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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|