none
attaching DB without .ldf file ???

    Question

  •   hi !!

    I want to attach database to SQL Server, but  I'hv only .mdf file.

    I lost .ldf file, how should i attach this DB to server ?

    Thanks

    SP

     

     

    Saturday, August 12, 2006 7:27 AM

Answers

All replies

  • Take a look at sp_attach_single_file_db (or CREATE DATABASE ... FOR ATTACH in SQL Server 2005).

    Was the database shutdown cleanly?

    Saturday, August 12, 2006 2:22 PM
    Moderator
  • You can attach a database without the logfile by attaching, then clicking the logfile location, and click the remove button. After you click OK a new logfile will be created for your database, like so:

    http://img.photobucket.com/albums/v472/Schmedrick/attach.gif

     

     

    Sunday, August 13, 2006 5:14 AM
  •  hi !!

    thanks for reply.

    I tried both the solution , but i am getting error -

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL\data\Settings_Log.LDF" may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down

     

    I don't hv LOG file of database , only hv mdf  file.

    Is there any other solution

    SPD

     

     

     

    Monday, August 14, 2006 7:03 AM
  • If the database wsa not cleanly shut down, and you do not have the log file, you will have some lost data and possible inconsistencies.

    The best method to use depends on which version of SQL Server you're running.  If you can let us know that, we'll take the next step.

    Monday, August 14, 2006 5:29 PM
    Moderator
  • I'm having a similar problem. I'm using sql server 2000 and my database has 2 log files. I lost one and when I trying attach without log file I got error !

     

    Regards,

     

    Alexandre A.

    Thursday, August 17, 2006 12:55 PM
  • ok - in that case, becase your database wasn't cleanly shutdown you'll need to use the last resort (assuming you're using SQL Server 2005)

    • create a database of equal size to the one you're trying to attach
    • shutdown the server
    • swap in the old mdf file
    • bring up the server and let the database attempt to be recovered and then go into suspect mode
    • put the database into emergency mode with ALTER DATABASE
    • run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair

    Your database will be available again but you'll have lost data and the data won't be transactionally consistent - see the following blog posts:

    If you're on SQL Server 2000, you can still do this but you'll need to use the undocumented DBCC REBUILD_LOG at your own risk. If you're unsure about any of this, you should contact Product Support to help you.

    Thanks

    Friday, September 01, 2006 6:49 PM
    Moderator
  • Hi ,
    This is working fine. But i want to attach without shutdown the sql server. Is it possible.
    Thanks.

     Paul Randal - MSFT wrote:

    ok - in that case, becase your database wasn't cleanly shutdown you'll need to use the last resort (assuming you're using SQL Server 2005)

    • create a database of equal size to the one you're trying to attach
    • shutdown the server
    • swap in the old mdf file
    • bring up the server and let the database attempt to be recovered and then go into suspect mode
    • put the database into emergency mode with ALTER DATABASE
    • run DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) which will rebuild the log and run full repair

    Your database will be available again but you'll have lost data and the data won't be transactionally consistent - see the following blog posts:

    If you're on SQL Server 2000, you can still do this but you'll need to use the undocumented DBCC REBUILD_LOG at your own risk. If you're unsure about any of this, you should contact Product Support to help you.

    Thanks

    • Proposed as answer by Ma77o Tuesday, April 16, 2013 8:11 PM
    • Unproposed as answer by Ma77o Monday, December 09, 2013 4:48 PM
    Tuesday, July 24, 2007 5:31 AM
  • When I started SQL Server and tried following code:

     

    Code Snippet

    Use master

    go

    sp_configure 'allow updates', 1

    reconfigure with override

    go

    sp_dboption 'MainBase', 'single_user', 'true'

    go

    alter database MainBase set EMERGENCY

    go

    DBCC CHECKDB('MainBase', REPAIR_ALLOW_DATA_LOSS)

    Go

     

     

    I got an error:

     

    Msg 922, Level 14, State 1, Line 1

    Database 'MainBase' is being recovered. Waiting until recovery is finished.

     

    What should I do?
    Thursday, July 26, 2007 5:07 PM
  • just wait for sometime as the database is currently recovering.i.e its rolling forward the committed transactions and rolling back the uncommited trans....it will recover soon

    Thursday, July 26, 2007 5:38 PM
    Moderator
  • I was waiting for 11 hours but nothing happened. I think there is no point in waiting becouse the database isn't really recovering.

     

    Is there any possibility to change sysdatabases.status = 32768 (emergency mode)? Currently sysdatabases.status = 70656

     

    Friday, July 27, 2007 6:03 AM
  • i dont know whether you can change the db into emergency mode coz its currently recovering,anyways try

    alter database dbname set emergency

    if its not working try to bring the db online by,

    restore database dbname with recovery but i am not sure if this works?...........cool

     

    Friday, July 27, 2007 6:13 AM
    Moderator
  • No. It doesn't work. I can swich th status between OFFLINE and

    RECOVERY PENDING =((

     

    May be I can detach it and try to attach using MSSQL2000 instead 2005?

    Friday, July 27, 2007 7:10 AM
  • and if my sql server is mssql 2000 what should i do
    help me i'm lost
    it's the db of 1 game
     please
    Saturday, December 15, 2007 9:16 PM
  • It may work if you update sysdatabases which has status data directly like this article. Make sure you have to execute undocumeted DBCC REBUILD_LOG instead of DBCC CHECKDB on SQL2K.

    Saturday, December 22, 2007 3:02 AM

  • It Seems you have some "uncommitted" transactions are there.



    Wednesday, April 23, 2008 9:36 AM
  • Will this procedure also work inside Small Business Server 2003 using SharePoint 2.0? I believe that SharePoint is based on the SQL 2000 platform....I have the same issue with my database having a corrupt .ldf file.

    Wednesday, May 21, 2008 2:03 PM
  • Thursday, May 29, 2008 3:27 PM
  • I would say that this moment your DB should be in SUSPACT mode than.You can attach a database without the logfile by attaching, then clicking the logfile location there click on remove button. Once you hit OK a new logfile will be created on the .ldf file location for the database.

    Thursday, May 29, 2008 7:25 PM
  •  

    A SQL 2000 database is a SQL 2000 database, but remember that a database which was not shut down cleanly and doesn't have a good log file WILL have corruption and lost data.  Just because you get the database up doesn't mean you're home free.  You should immediately run a full CHECKDB to see what shape you're in.
    Friday, May 30, 2008 4:28 AM
    Moderator
  • But even CHECKDB isn't enough, right? I'm thinking half-baked transactions and such. For instance and order for which we have the order header but not the ordr details rows.

     

    Friday, May 30, 2008 4:46 AM
    Moderator
  • I've worked around this problemt like what you said but I got below message. How could I restore my database with other way?
    Thanks.
    Code Snippet

    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\CommunityServer_log.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
    Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\Community Server\CSDatabase\CommunityServer_log.LDF'.
    Msg 5024, Level 16, State 2, Line 1
    No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
    Msg 5028, Level 16, State 2, Line 1
    The system could not activate enough of the database to rebuild the log.
    DBCC results for 'CommunityServer'.
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'CommunityServer'.
    Msg 7909, Level 20, State 1, Line 1
    The emergency-mode repair failed.You must restore from backup.


    Wednesday, August 20, 2008 4:40 AM
  • Hello,

     

    I have the same situation as described above: (i.e. a mdf file which has 2 logs). I have detached the database. Made a copy of the database on another server. The database is approx. 160 GB and takes a long while to copy across servers. I copied both the logs over after a certain period of time.

     

    When I tried to attach on the new server with the new location of the data & 2 log files, I got an error saying the log file does not match the data. So I removed the logs and figured that since this is not a transactional database anyways, if I do not put the log file name, one will get generated automatically (as was the case with all of my databases which were moved over and had only 1 log).

     

    However, in this case because of 2 log files, an automatic creation of the log file does not happen.

     

    So I followed the above steps and set the database (it is SQL 2005) in emergency mode through Alter database

    Then I ran DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS).

     

    I get the following errors:

     

    Msg 5173, Level 16, State 1, Line 1
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files.  If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file 'D:\Logs\DW_log.ldf' does not match the primary file.  It may be from a different database or the log may have been rebuilt previously.
    File activation failure. The physical file name "M:\SQLLog\DW2_Log.LDF" may be incorrect.
    Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\SQLLog\DW_Log.LDF'.
    Msg 5024, Level 16, State 2, Line 1
    No entry found for the primary log file in sysfiles1.  Could not rebuild the log.
    Msg 5028, Level 16, State 2, Line 1
    The system could not activate enough of the database to rebuild the log.
    DBCC results for 'DW'.
    CHECKDB found 0 allocation errors and 0 consistency errors in database 'DW'.
    Msg 7909, Level 20, State 1, Line 1
    The emergency-mode repair failed.You must restore from backup.

     

    Any help in this matter would be appreciated.

     

    PS. We are having some issues restoring from backups due to a host of reasons I wont go into.

     

    Thank you

    Tuesday, August 26, 2008 9:28 PM
  •  

    Hey ShitalP -

    I had the same issue as of recently, Follow these steps:

     

    1.) Move your DB file into:

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    - This is where SQL Server stores its DB's

     

    2.) Right-Click on your DB  >  Properties  >  Uncheck Read-Only

    - This is whats keeping SQL Server from creating a new log file for you.

     

    3.) Attach the DB

    a.) Attach the DB the way you have been doing before

    b.) As stated in a previous post click on the log file and press the remove button

    c.) Press OK

     

    After that you should be good to go.

     

    - Ryan

     

     

    • Proposed as answer by jhaebets Thursday, January 21, 2010 8:21 AM
    Wednesday, October 29, 2008 2:33 PM
  • I'm using Windows Server 2008 and Visual Studio 2008 on a web project created in Visual Studio 2005 using SQLExpress Advanced 2005 SP3.  I am trying to attach the Website's MDF into SQLExpress 2008 Advanced using a standard installation as closely matching the 2005 installation (in another partition) as I can manage.

    The path to the 2005 version was "Documents\Visual Studio 2005\Projects\WebSite.com\App_Data".  In this 2008 partition I am using "Documents\Visual Studio 2008\Projects\WebSite.com\App_Data".  I went into the SQL Advanced IDE and tried to attach the MDF with its LDF in a regular way by right-clicking on Databases and selecting Attach.  I get an error saying the primary database file is read-only - it is not read-only.  Then I tried deleting the LDF and using the Attach template and executed the following query:

    IF NOT EXISTS(
    SELECT *
    FROM sys.databases
    WHERE name = 'dbName'
    )
    CREATE DATABASE dbName
    ON PRIMARY (FILENAME = 'x:\Documents\Visual Studio 2008\Projects\WebSite.com\App_Data\dbName.MDF')
    FOR ATTACH

    The Messages box then read:

    File activation failure. The physical file name "x:\Documents\Visual Studio 2005\Projects\WebSite.com\App_Data\dbName_log.ldf" may be incorrect.
    The log cannot be rebuilt when the primary file is read-only.
    Msg 1813, Level 16, State 2, Line 9
    Could not open new database 'dbName'. CREATE DATABASE is aborted.

    I was telling it to look in the 2008 folder and it is reporting on the 2005 folder.  I ran this many, many times to make sure I was not dreaming or something.

    I went so far as to actually create this 2005 folder and moved the Website into it.  When I ran the query again, the resulting message was identical. 

    Guys, I've been at this SQL Server and Visual Studio thing for a really long time.  How are we, as developers, supposed to contend with ____ (incredibly sugar-coated) like this?  It's not even a beta release - I got it all in the Partners Action Pack.  I don't expect a response - I never have gotten one on these forums anyway but I'm hoping someone with the right influence will prevoke an increase in the testing done on these MS products.  It's bad enough having to fight the lack of information on new technologies without having to fight inoperative tools as well.  It should look where I said - period.

    • Proposed as answer by Ulhas Morbale Thursday, October 27, 2011 9:50 AM
    Tuesday, March 17, 2009 7:50 PM
  • Perhaps the SQL Server service account doesn't have apropriate permissions on that folder?
    Tibor Karaszi
    Wednesday, March 18, 2009 3:26 PM
    Moderator
  • I don't suspect that because I was able to create a database in that folder.  After I created it, I stopped the service, pasted a populated copy of the database over the new one, restarted the service and all was well.  Unless there is a distinction between creating a database and reading one, something else has to be amiss - I'm suspecting there must be internal permissions in the database itself beyond the scope of password protection.  This database had nothing done to its permissions other than default settings for Windows Authentication and I always use the same username and password on my different partitions.  So, color me stumped.

    Regardless of the permissions, nothing can explain away the fact that SQL Server was looking in the wrong folder.
    • Edited by jbarton Wednesday, March 18, 2009 4:46 PM Left something out.
    Wednesday, March 18, 2009 4:33 PM
  • I ran into the same issue. Check the following link out. http://blog-rat.blogspot.com/2009/04/how-to-attach-mdf-without-ldf-into-sql.html My issue was resolved now.
    Monday, April 13, 2009 3:40 PM
  • What is the reason we need to create a database of equal size of the old one?  Can it be any size? Please advise. Thanks. 

    Saturday, October 10, 2009 4:56 PM
  • Andy T

    removing old ldf file works.....  automatically it will create a new ldf... :)

    Thanks




    Monday, December 28, 2009 5:04 AM
  • <<removing old ldf file works.....  automatically it will create a new ldf... :)>>

    Only if the database was cleanly shutdown and the primary file internally indicates tha no recovery work if needed during startup. The thing is that we don't know in advance whether this is the case. Deleting an ldf file is an extremely risky thing to do (I like to think of it as playing Russian roulette). We see countless reports in various forums where "the logfile was lost" and the database cannot be recovered so restoring from a backup is the last resort.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Monday, December 28, 2009 7:08 AM
    Moderator
  • Thanks Andy , the attached image helped me attaching a databasse without a ldf file.
    Friday, October 08, 2010 10:14 AM
  • Method-
    Use the following command and run it:
    Sp_Attach_Db: Sp_Attach_Single_File_Db [ @Dbname = ] 'Dbname' , [ @Physname = ] 'Physical_Name'

    Example:


    EXEC sp_attach_single_file_db @dbname = 'Test',
    @physname = 'C:\Microsoft SQL Server\MSSQL\Data \Test.mdf'

    • Proposed as answer by Vihang Shah Sunday, January 16, 2011 10:38 AM
    Tuesday, January 11, 2011 9:15 AM
  • You're a life saver!!!! Been battling with it for almost 5 hours....i looovee you.
    Sunday, March 18, 2012 12:27 AM
  • Thanks Ryan.. The solution worked.
    Thursday, June 28, 2012 10:48 AM
  • I would also like to point out: make sure you are running SQL Management Studio as an administrator so that it has access to the files, if it can't create a new log file (says read-only), its because it doesn't have permission to create one!
    • Proposed as answer by MrDS83 Friday, January 24, 2014 8:12 PM
    Friday, October 04, 2013 5:29 PM