Attach MDF file with no LOG

  • Balmukund Lakhani (3/31/2006)


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

  • I am trying to mount a database (SQL Server 2005) from a set of data files (an mdf file, a number of ndf files and an ldf file) from the same database on another server.

    I have tried using the attach utility from the SQL Server Management Studio, specifying the mdf file in the top pane which automatically lists the ndf and ldf files in the lower.

    I keep getting an error 'Could not open new database 'continuum'. CREATE DATABASE is aborted. The physical file name "h:\......\......continuum_log.ldf" may be incorrect. The log cannot be rebuilt because the database was not cleanly shut down.'

    Can someone please provide step/tips on where I am going wrong or how to create a new log.

  • An easier way would be using the Management studio and in the attach database window highlight the log file and then remove it. Press ok after the database is attached in new log file.

  • That only works if the database was cleanly shut down - i.e. the transaction log doesn't have any transactions that need to be rolled back. In this case the database was not cleanly shutdown so your advice won't work.

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Using SSMS is the simplest way to attach MDF file without LDF file. I mentioned some steps in which you can execute this task.

    Firstly open the SSMS and then choose databases an object explorer item

    Then right click on it and choose attach option

    A window appears on the screen click on add then suddenly browse the MDF file

    As on selecting MDF file a confirmation window opens confirm it. In this window the in downward side two files are list i.e. MDF and LDF file. Select the LDF file and remove it. Then click on OK

    MDF file successfully attached you can verify in the databases list.

    SSMS Expert

Viewing 5 posts - 16 through 19 (of 19 total)

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