Attaching Multiple MDF and having LDF regenerated

  • Rob Scholl

    Old Hand

    Points: 300

    There are a bunch of posts for attaching a single MDF and having the LDF regenerated using sp_attach_single_file_db.

    Can anyone see anything wrong with this approach for doing the same thing with multiple MDFs?

    1. Detach the database

    2. Delete or move the log file to a new location so it will not be found.

    3. Run the sp_attach_db command

    EXEC sp_attach_db 'delme',N'C:\DATA\Delme.mdf',N'C:\DATA\Delme2.ndf' 

    You will get a file activation failure message that there was an error and a new log file was created, however it will create it in the MDF directory.e.g. File activation failure. The physical file name "C:\LOG\delme_log.ldf" may be incorrect. New log file 'C:\DATA\delme_log.ldf' was created.

    4. Detach the database again

    5. Move the new log file to the desired location

    6. Rerun the sp_attach_db command and provide the log file path

    EXEC sp_attach_db 'delme',N'C:\DATA\Delme.mdf',N'C:\DATA\Delme2.ndf',N'C:\LOG\delme_log.ldf'



  • Steve Jones - SSC Editor

    SSC Guru

    Points: 718163

    Seems like that works for me.

  • Rob Scholl

    Old Hand

    Points: 300

    Thanks Steve 🙂

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply