SQL Server Performance

Restore Database from MDF file without LDF

Discussion in 'General DBA Questions' started by Shansinn, May 19, 2005.

  1. Shansinn New Member

    Iam having SQL Database MDF file how can i restore the database without LDF.Please anyone reply me and this is very very urgent [:0]
  2. dineshasanka Moderator

    sp_attach_single_file_db

    EXEC sp_attach_single_file_db @dbname = 'pubs',
    @physname = 'c:program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf'
  3. Shansinn New Member

    Thanks for the rply.That i have treid and its showing the error.

    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'db_name'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'D:program FilesMicrosoft SQL ServerMSSQLdataTraining_Log.LDF' may be incorrect.
  4. ranjitjain New Member

    As said by dinesh, sp_attach_single_file_db has to work,
    but u can still try attching it thru EM.
    right click on database and select attach DB from all task
    and specify the MDF path check whether this helps in ur case.
  5. dineshasanka Moderator

  6. Shansinn New Member

    In this linkhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8164 there is no solution. I have also got the same error while trying to attach the MDF file.

    Server: Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'MyxxDb'. CREATE DATABASE is aborted.
    Device activation error. The physical file name 'D:program FilesMicrosoft SQL ServerMSSQLdataTraining_Log.LDF' may be incorrect.
  7. dineshasanka Moderator

    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/col...al.com/scripts/scriptdetails.asp?scriptid=599
  8. Shansinn New Member

    I dont have any log(LDF) files with me.Iam having only MDF.
  9. dineshasanka Moderator

    did u run the script that come in the second link
  10. Shansinn New Member

    If I try to run the script

    dbcc traceon(3604)
    dbcc rebuild_log ('Training','Training_LOG')

    It is showing the error
    ================Error====================
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Server: Msg 2520, Level 16, State 4, Line 2
    Could not find database 'Training_Data'. Check sysdatabases.
    Server: Msg 5105, Level 16, State 1, Line 2
    Device activation error. The physical file name 'Training_LOG' may be incorrect.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  11. Shansinn New Member

    Sorry Its showing this Error.In the previous Query Instead of the DataBase Name Training I used Training_Data

    After executing its showing this error
    ====================Error=================
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Server: Msg 5105, Level 16, State 2, Line 2
    Device activation error. The physical file name 'Training_LOG' may be incorrect.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  12. dineshasanka Moderator

    is it posible you to send me the mdf file.
    if so send it to dineshasanka@gmail.com

    quote:Originally posted by Shansinn

    Sorry Its showing this Error.In the previous Query Instead of the DataBase Name Training I used Training_Data

    After executing its showing this error
    ====================Error=================
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Server: Msg 5105, Level 16, State 2, Line 2
    Device activation error. The physical file name 'Training_LOG' may be incorrect.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  13. Shansinn New Member

    please check ur mail
  14. Ace74 New Member

    I know this is an onld post but if anyone needs 2005 solution, please ask.
  15. TommyT81 New Member

    I would love the solution.
  16. Adriaan New Member

    Similar message may occur if you try to restore a SQL 2005 backup to a SQL 2000 instance (which will never work).
  17. punkin New Member

Share This Page