I'm trying to attach a MDF file with no LOG (log file has been deleted). Normally SQLServer creates a new LOG file. But this time I get this message:
There is no FCT_ENTIDADES_Log.LDF in the Data directory.
Can someone give me a hand on this?
execute this query with the respective information
EXEC sp_attach_single_file_db @dbname = '', --- your database name @physname = '' --- the location of the .mdf file
Thanks and have a nice day!!!
Thank you for replying.
That's precisely the script i'm executing...
Then you try to attach the same through Enterprise Manager.
Right Click on databases -> all tasks -> attach database
a new window will be opened. Give the location of the .mdf file
In the next block, you can see the original file names and physical file names. here you uncheck the .ldf file and speacigy the database name to attach as and the database owner.
Hope it will work now.
I've this one indeed. But i obtain the same message in the dialog box.
Thanks for your help.
When u attach the db with no log file, the device activation error occurs and sql server creates a new log file..
After executing the attach statement, exec sp_helpdb 'dbname' should give you the locations of the files..
Correct me if I am wrong, but if the database originally had more than one physical file for the log then attach command will not recreate the log file. This only seems to work if you have only one physical file for your log. At least that has been the case for me.
There is no easy way of doing it.
I am sure that database was not cleanly shutdown so you won't be able to use only mdf file to recover the database (sp_attach_single_file_db)
Do below steps.
== I detached ran for pubs database files==
DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 3)
dbcc rebuild_log('dbname','New Log File NAme')
THIS IS UNDOCUMENTED COMMAND.
database will come into Single User mode
Run dbcc checkdb and if there are problems then you need to restore from backup