SQL Server Performance Forum – Threads Archive

Urgent question – restore db from only .mdf file

guys: It’s really urgent. Can anyone give me advice on how to restore db form only .mdf file.
( .ldf file has been deleted!) I tried sp_attach_single_file_db, but there is error Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database . CREATE DATABASE is aborted.
Log file ‘CATABASEMSSQLdatadbname_Log.LDF’ does not match the primary file. It may be from a different database or the log may have been rebuilt previously. Many thanks!!! Wait online!!

---

sp_attach_single_file_db @dbname = ‘RA’
, @physname = ‘Crogram FilesMicrosoft SQL ServerMSSQLDataRMT.mdf’
I used as above it works fine
can you paste yoor code here please

---

the problem is I didn’t use the sp_detach_db to detach database!!! I lost log file then can’t start db any more.

---

Yes i delete the log file and re did it again. Then I got a warning and it creates the db
warning is Device activation error. The physical file name ‘Crogram FilesMicrosoft SQL ServerMSSQLdatasqlbpa_log.LDF’ may be incorrect.
New log file ‘Crogram FilesMicrosoft SQL ServerMSSQLDataRA1_log.LDF’ was created.

---

thanks for your help, dineshasanka
can you try delete the log before starting sqlserver? thanks again.

---

sure. I will contact you soon

---

Device activation error. The physical file name ‘Crogram FilesMicrosoft SQL ServerMSSQLDataGPLDT1.ldf’ may be incorrect.
New log file ‘Crogram FilesMicrosoft SQL ServerMSSQLDataRA_log.LDF’ was created. Same thing as per BOL
sp_attach_single_file_db
Attaches a database having only one data file to the current server. so it should work. I don’t think you have a permission issue. Do you?

---

Did you find a solution??????
I have the same problem.
my MDF file is not a detached MDF.
I have only (.MDF) file and no log file in the directory.
But it tries to find .LDF file. sp_attach_single_file_db ‘UZMAN’ , ‘c:cddataUZMAN2005_DATA.mdf’ Error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘UZMAN’. CREATE DATABASE is aborted.
Device activation error. The physical file name ‘C:cddataUZMAN2005_Log.LDF’ may be incorrect
cemuney

---

hi cemuney,
the solution dinesh has provided has to work even i have tested on my machine
and its working perfectly after connecting it from sp_attach_single_file_db.[:I]

---

The SP sp_attach_single_file_db will recreate the log file .LDF freshly and it is the best way to accomplish when .LDF file is missing. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

haven’t found any solution, so have to create the data (import a 220million table) but still lucky
which took me 1 more day.

---

How many log files do you hae attached to the database
if there are more than one log files this will not work
http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp Try this script
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599

---

Hi everyone. dineshasanka:I had only one mdF and LDF file.
I couldnt find any solution without LOG file. I found my old LDF file and attach database. sp_attach_single_file_db was not enought for my problem with only MDF.
Because database recovery model was not (SIMPLE) so SQL needs LOG file to ROLLBACK the transactions. I copy the LOG file in the same directory and use sp_attach_single_file_db
and it attached the db. And said 107 transactions rolled back successfully.
You can not use sp_attach_single_file_db only with MDF file If your database recovery model was (FULL) and you shut down the Computer suddenly and you lost the log file. If anyone want to see the problem i can put the MDF file anywhere on the internet or mail it(3.7MB) Thanks for everyone

---

can you mail it to me to [email protected].com

---

ok dineshanka i will send it to you tommorrow. i dont have file now. thanks.

---

Ok no problem,
I am waiting for it. there is another post with same question.
I was trying to create this in my pc which was failed.
SO I am looking forward to your file
Thankx

---

ok dineshanka
i create the MDF file i am sending you the file MDF. i am sending u MDF . —————————–
sp_attach_single_file_db ‘cemuney’,
‘Crogram FilesMicrosoft SQL ServerMSSQLDATAcemuney_data.mdf’ it gives
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘cemuney’. CREATE DATABASE is aborted.
Device activation error. The physical file name ‘Crogram FilesMicrosoft SQL ServerMSSQLdatacemuney_Log.LDF’ may be incorrect.
——————————–

---

Yes you are correct
u can use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.
But the script that i gave you should work . I will try and let you know tommorow.
Thankx

---

thank you very much for your help. I will have a try.

---

dineshasanka, What is the script/solution you provided? I received a MDF from a client, and trying to restore it to my server. I did NOT have this db in my server before, nor did I detach it from my server.
No idea whether the client’s db had one or more log files, or their db is in simple or full recovery mode. I tried both methods of sp_attch, and also thru EM, it gives me the same error
Device activation error. The physical file name ‘D:Microsoft SQL ServerMSSQLdataxxx_log.ldf’ may be incorrect. I even created this directory. I also created a new db with the same naming conventions in my server, and then I detached it. replaced the mdf file with the mdf file I got from my client. I tried attaching, same error. I tried this in both single and full recovery mode. Any other help greatly appreciated.
bj

---

Hi All guys I am facing same problem. I have only MDF file cos of power failure and when I started my PC I couldn’t able to start Database even can’t access to it. so what I did that format my PC but still have my MDF file in D drive. After reinstall every thing I try to attach this MDF file using ‘sp_attach_single_file_db’.
Unfortunately, I got message
How can get my database restore?? please help. Thanks in advance

---

You need to have a good backup in order to restore the file.
Also make sure the SQL services account has required privilege on the specified path, if mydb_log.ldf exists then drop it and try again. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi sonny
1.Does your database marked as SUSPECT on E.Manager?
2.What was your database recovery Model?

---

Thanks satya…
mydb_log.ldf is not exist like said format the ‘C’ drive. I had Data file on ‘D’ drive and Log file on ‘C’ drive. Therefore, only mdf file I have. I could get the latest backup cos of power failure.. I know it is not done but it is developement server. Thanks cemuney..no my database is not marked as suspect. database was in FULL recovery mode. suggest some solution it is urgent…..

---

hi sonnysingh In power failure.
it is not possible to use ‘sp_attach_single_file_db’ if your database recovery model.
Because database needs LOG file to recover database in FULL RECOVERY MODEL. Sometimes in suddden power failure it becomes a problem. That was also my big problem about my customers.
These are the steps i recover my databases. i rescue the records with this method.
I hope it works on your problem.. 1.Create an empty <<MYDB>> database in Enterprise Manager(EM). Empty MYDB_Data.mdf and MYDB_Data.ldf will be created.
2.Stop SQL Server. That overwrite your original MYDB_Data.mdf file to this new empty file.
3.Start SQL Server. Open You will see your database marked as <<Suspect>> in EM.
4.Open Query Analyszer. In master database execute. sp_configure ‘allow updates’, 1
reconfigure with override
GO UPDATE SYSDATABASES SET STATUS=-32768 WHERE NAME=’MYDB’
GO 5.Now your database is in <<Read OnlyOfflineEmergency Mode>>
6.Because of being READONLY. You have to export your records with DTS Import/Export wizard,
Also you can execute <<SELECT>> queries in your database. Please inform us if it works.
Cem Uney

---

Thanks Cem Uney.. it is work and able to get the database on the stage as you have mentioned. I have script the database (structural as well as data) using ApexSQL Script and rebuild the database again. Tell me this is the only option we have to update the database
can not we set status to that put the database in normal state??? Regards and thanks again Cem Uney

---

**REmoved as it stands advertisement

---

Hi
Check if this helps..
http://social.msdn.microsoft.com/Fo…y/thread/189be01f-23de-48b0-96cc-8f1292c13c54

---

Hi Rohit,
Kindly check the post date before answer any question.
Thank you,

---

Hehehehe…..
THx luis for reminding me……

---

I faced this problem yesterday in sql server 2000 sp4 , and sp_attach_single_file_db was not helpfull at all
I had to restore database from backup.
– that is the case:
-The database is full recovery model
-a long running transaction was stoped by power failure
-when server start , database become in suspect mode
-i deattached the database
-the log file is not accessible
-i could not reattch it ,and get same error
i think that the log file is needed in such cases with the mdf , and no way except restore from backup

---

]]>