SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Attach MDF file with no LOG


Attach MDF file with no LOG

Author
Message
Vitor José Badalinho
Vitor José Badalinho
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

Greetings,

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:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'FCT_ENTIDADES'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\FCT_ENTIDADES_Log.LDF' may be incorrect.

There is no FCT_ENTIDADES_Log.LDF in the Data directory.

Can someone give me a hand on this?

Thanks


lucky-80472
lucky-80472
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 842

Hello,

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!!!




Lucky
Vitor José Badalinho
Vitor José Badalinho
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

Hi.

Thank you for replying.

That's precisely the script i'm executing...


lucky-80472
lucky-80472
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 842

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.




Lucky
Vitor José Badalinho
Vitor José Badalinho
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

Hello.

I've this one indeed. But i obtain the same message in the dialog box.

Thanks for your help.


Krishnan Kaniappan
Krishnan Kaniappan
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 286

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..



-Krishnan
buddy__a
buddy__a
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 257
If the files were not detached (using sp_detach_db) succesfully then you cannot not use sp_attach_db. The key here is detached "successfully".



Dave Foster
Dave Foster
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 1

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.


Balmukund Lakhani-269523
Balmukund Lakhani-269523
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 367

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.

  • Run the command for MDF file and get the logical and physical name of Log file.

== I detached ran for pubs database files==

DBCC CHECKPRIMARYFILE('C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 3)

  • Create a database with same physcial and logical file name.
  • Stop SQL Server.
  • Keep old mdf file on same location as of new database and rename log file.
  • Start SQL Server.
  • Database would come up in suspect mode.
  • Since Log File will not be linked to the new MDF File you have to run DBCC REBUILD_LOG

    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

HTH


buddy__a
buddy__a
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 257
This would have been good information to know two months ago. I love undocumented commands, especially when they have been documented.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search