SQL SERVER – Attach mdf file without ldf file in Database

Background Story:
One of my friends recently called up and asked me if I had spare time to look at his database and give him a performance tuning advice. Because I had some free time to help him out, I said yes. I asked him to send me the details of his database structure and sample data. He said that since his database is in a very early stage and is small as of the moment, so he told me that he would like me to have a complete database. My response to him was “Sure! In that case, take a backup of the database and send it to me. I will restore it into my computer and play with it.”

He did send me his database; however, his method made me write this quick note here. Instead of taking a full backup of the database and sending it to me, he sent me only the .mdf (primary database file). In fact, I asked for a complete backup (I wanted to review file groups, files, as well as few other details).  Upon calling my friend,  I found that he was not available. Now,  he left me with only a .mdf file. As I had some extra time, I decided to checkout his database structure and get back to him regarding the full backup, whenever I can get in touch with him again.

Technical Talk:
If the database is shutdown gracefully and there was no abrupt shutdown (power outrages, pulling plugs to machines, machine crashes or any other reasons), it is possible (there’s no guarantee) to attach .mdf file only to the server. Please note that there can be many more reasons for a database that is not getting attached or restored. In my case, the database had a clean shutdown and there were no complex issues. I was able to recreate a transaction log file and attached the received .mdf file.

There are multiple ways of doing this. I am listing all of them here. Before using any of them, please consult the Domain Expert in your company or industry. Also, never attempt this on live/production server without the presence of a Disaster Recovery expert.

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

Method 2: If one or more log files are missing, they are recreated again.

There is one more method which I am demonstrating here but I have not used myself before. According to Book Online, it will work only if there is one log file that is missing. If there are more than one log files involved, all of them are required to undergo the same procedure.

-- Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Please read the Book Online in depth and consult DR experts before working on the production server. In my case, the above syntax just worked fine as the database was clean when it was detached. Feel free to write your opinions and experiences for it will help the IT community to learn more from your suggestions and skills.

PS: Do not try this on production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

85 thoughts on “SQL SERVER – Attach mdf file without ldf file in Database

  1. Very timely – I just had a SAN vanish from my DR environment (don’t ask), and had to replace the MDF files. You have to DROP the database first, as SQL insists it is there (and online, no less), and then use Method 1.
    As a great engineer once said, the more complicated they make it, the easier it is to stop up the plumbing.

  2. When executing this, getting error Device not ready

    EXEC sp_attach_single_file_db @dbname=’TestDb’,
    @physname=N’D:\Temp.mdf’
    GO

    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “D:\Temp.mdf” failed with the operating system error 21(The device is not ready.).

    How to resolve?

  3. Assuming there really is a Drive D:, I suspect a permissions problem. See what account your SQL instance is running under and make sure it has read/write access to the D:\ drive.

    I’m generally not a fan of putting files in the root directory of a drive, you probably want to make a folder.

    Also, make sure it is an NTFS drive and not FAT32 (unlikely, but possible). Not sure, but perhaps the newer versions of SQL Server don’t like FAT32 drives.

  4. Thanks I was searching on the web since yesterday and tried couple of other methods as well.
    This one worked like a charm.
    Cheers

  5. When executing this, getting error File activation failure

    EXEC sp_attach_single_file_db @dbname=’platinumlive’,
    @physname=N’F:\Hotel Management Soft\platinumlive.mdf’

    File activation failure. The physical file name “D:\DataBase Backup\platinumlive_log.ldf” may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘platinumlive’. CREATE DATABASE is aborted.

    How to resolve?

  6. When executing this, getting error File activation failure

    EXEC sp_attach_single_file_db @dbname=’platinumlive’,
    @physname=N’F:\Hotel Management Soft\platinumlive.mdf’
    Go

    File activation failure. The physical file name “D:\DataBase Backup\platinumlive_log.ldf” may be incorrect.
    The log cannot be rebuilt because the database was not cleanly shut down.
    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘platinumlive’. CREATE DATABASE is aborted.

    How to resolve?

  7. Method 1 worked like a charm, thanks.
    Remember guys to refresh the object explorer so it shows the db attached with the new ‘empty’ ldf.

  8. Hi everybody,
    im using the first method:
    USE [master]
    GO
    — Method 1: I use this method
    EXEC sp_attach_single_file_db @dbname=’TestDb’,
    @physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\Mydatabase.mdf’
    GO

    but i have this error:
    Msg 3415, Niveau 16, État 3, Ligne 1
    Impossible de mettre à niveau la base de données ‘TestDb’ car elle est en lecture seule ou contient des fichiers en lecture seule. Rendez la base de données accessible en écriture ou réexécutez la récupération.
    Msg 1813, Niveau 16, État 2, Ligne 1
    Impossible d’ouvrir la nouvelle base de données ‘TestDb’. Abandon de CREATE DATABASE.

    Someone can help me please how can i fix that!!!!!!!!!

  9. plz any1 can help me i m in v trouble, i try my best but i am not successful. i have code and database i want my application update mdf file from diff. system. at least of 2 computer network can access .mdf from different location.
    i want to put my mdf file in server and my code or application access from different computers attached in network
    plz help me
    i m not professional i m just seeker

  10. It is very dangerous if anyone follows this in production. Please highlight at the top of the post that it should not be done in prodution environment and data loss may occur! I hope no one did it in production environment.

  11. It is very dangerous if anyone follows this in production. Please highlight at the top of the post that it should not be done in prodution environment and data loss may occur!
    I just hope no one did it in production environment.

  12. I have a mdf file from a crashed machine and when I try to restore using different tricks I keep getting the error “The log cannot be rebuilt because the database was not cleanly shut down” . Please let me know if you have a solution to restore the DB.

  13. One problem I’m getting is this:
    “File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf” may be incorrect.”

    It seems to me that SQL Server is looking for the log file on a default folder that doesn’t exist in my installation of SQL Server. Any tips? Thank you!

  14. Ok, I found my own answer. I used method 1 from above. This is what I did:

    1. Copied the .mdf file to the DATA folder of the server instance I’m using under SQL Server.

    2. I used method 1 from Pinal’s description.
    (I had Management Studio opened as Administrator, but I think this may not be necessary if you copy to a folder where SQL Server has permissions to create a log file)

    3. I got an error referring to SQL not being able to create a log file in a default folder, however it created the log file in the DATA folder of my current instance. After refreshing I could see and query my new database.

    This was the message:
    “File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf” may be incorrect.
    New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.Mydatase\MSSQL\DATA\AdventureWorks2008R2_Data_log.LDF’ was created.”

    Now I can work. Thank you so much!

  15. Directory lookup for the file “D:\database1.mdf” failed with the operating system error 21(The device is not ready.).

  16. File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10_50.ACC\MSSQL\DATA\test_1.LDF” may be incorrect.
    Msg 5123, Level 16, State 1, Line 3
    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘e:\Test_log.LDF’.
    Msg 1813, Level 16, State 2, Line 3
    Could not open new database ‘Test’. CREATE DATABASE is aborted.

  17. Hello , what do I do with this situation?
    The log cannot be rebuilt because the database was not cleanly shut down

  18. I had a situation where non of those methods work, bu this method worked for me fine :
    1) Create database with same name as MDF file you have.
    2) Stop SQL Server and swap MDF files. Make sure you also keep new database you just created.
    3) Start SQL Server. Database will be now in suspect state because log file is not correct.
    4) Run the following script:

    USE [master]
    GO
    ALTER DATABASE [MyDatabase] SET EMERGENCY
    GO
    ALTER DATABASE [MyDatabase] SET SINGLE_USER
    GO
    DBCC CHECKDB ([MyDatabase], REPAIR_ALLOW_DATA_LOSS)
    GO
    ALTER DATABASE [MyDatabase] SET MULTI_USER
    GO
    ALTER DATABASE [MyDatabase] SET ONLINE
    GO

      • It is in the state of Emergency mode after SET EMERGENCY AND SET SINGLE_USER in step 4. It doesn’t work to run DBCC CHECKDB.

        When the time to run: DBCC CHECKDB (fay_fayetteville_tst, REPAIR_ALLOW_DATA_LOSS)

        I got following message:

        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\MSSQL10_50.SMTKINGDOM\MSSQL\DATA\fay_fayetteville_tst_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 5(Access is denied.) while attempting to open or create the physical file ‘\\seismicmicro\support\client_and_rti_data\SWN\fay_fayetteville_tst\ProjectDatabase\fay_fayetteville_tst_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 ‘fay_fayetteville_tst’.
        CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fay_fayetteville_tst’.
        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\MSSQL10_50.SMTKINGDOM\MSSQL\DATA\fay_fayetteville_tst_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 5(Access is denied.) while attempting to open or create the physical file ‘\\seismicmicro\support\client_and_rti_data\SWN\fay_fayetteville_tst\ProjectDatabase\fay_fayetteville_tst_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 ‘fay_fayetteville_tst’.
        CHECKDB found 0 allocation errors and 0 consistency errors in database ‘fay_fayetteville_tst’.
        Msg 7909, Level 20, State 1, Line 1
        The emergency-mode repair failed.You must restore from backup.

      • Thanks Shabab! Your post helped me.
        My problem was the transaction log file being deleted while the database is detached. I noticed that detaching locks the physical files.

  19. anyone new reviewing the commentary:

    I have used this exact method for years. Tried and true. This has worked for me, every time I have had to use it.

  20. Hi Guys..My server got crashed but i recovered the mdf and ldf file..when i try to attach the file..i got following error..

    the pageaudit property is incorrect. microsoft sql server error 5172

    The header for file ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Abakdata.mdf’ is not a valid database file header. The PageAudit property is incorrect.

    Can anyone help me to get rid out of this problem ??

    Thank you very much in advance..

  21. I just Attached .mdf file and removed .ldf, but database performance is very slow, how to improve performance, please suggest.

  22. Error :
    The database ‘shopping’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.

    for this type of error what i have to do
    please reply e

  23. I had a similar situation yesterday where my server had crashed due to some electric fault. all the db on that server were restored properly but one of the db was not restoring. moreover, the same was detached by one of my colleague.
    After detaching, the db was not reattaching as the ldf file seem to be corrupt.
    I did the following tasks:
    1. renamed the original db from orchid.mdf to orchid_old.mdf and orchid_log.ldf to orchid_log_old.ldf
    2. created another db with the same name “Orchid” with the files in the same path as that of the old
    3. Stopped the SQL server.
    4. deleted the newly created DB file (mdf only) from the path and renamed the old mdf file to original name: orchid.mdf
    5. now i restarted sql server services. This caused the db to use the old db file with the newly created ldf file for the new db.
    6. The database was in suspect mode still
    7. next i put this db in emergency mode and single user mode using the following commands:

    USE [master]
    GO
    ALTER DATABASE ‘orchid’ SET EMERGENCY
    GO
    ALTER DATABASE ‘orchid’ SET SINGLE_USER
    GO
    8. the next step is very important: i then opened the properties for the database and changed the logging method from simple to full mode
    9. again changed the db logging method from full to simple.
    10. The database was now working. next i put the db back in multi_user mode by using following command:
    ALTER DATABASE ‘orchid’ SET multi_USER
    GO

    all the above steps mentioned brought my db back in operation,

    hope this helps others who are not able to use dbcc tool for rebuilding the log file (being sql server 2005)

  24. I had a server crash and the backups didn’t run for the database backup. I was able to recover the mdf files, but not the ldf files.

    When attempting method 1 and method 2, I get the following errors.

    Msg 1813, Level 16, State 2, Line 1
    Could not open new database ‘TestDb’. CREATE DATABASE is aborted.
    Msg 824, Level 24, State 2, Line 1
    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:8). It occurred during a read of page (1:9) in database ID 5 at offset 0x00000000012000 in file ‘D:\SQL Server Database Primary Data File (mdf)0000006.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    I have used methods one and two before when restoring databases with no ldf files with no problems. I am not sure what I can do next.

  25. hi Pinal,
    master database is corrupted. I don’t have backup also. How to recover that database to start sql server instance? I tried to rebuild master database with no luck. got below error:
    Overall summary:
    Final result: Failed: see details below
    Exit code (Decimal): -2068643838
    Exit facility code: 1203
    Exit error code: 2
    How to start my instance in Local system? Please help

  26. thanks for all of your articles and samples, i use them frequently.
    this time it didn’t help
    but it gave me some ideas for other problems.
    in the end what works for me was regular restore database.

  27. sp_attach_single_file_db (Transact-SQL)
    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL). Do not use this procedure on a replicated database.

  28. Try to this way:

    USE [master]
    GO
    CREATE DATABASE [YourDB] ON
    ( FILENAME = N’C:\Data\yourdb.mdf’ )

    FOR ATTACH
    GO

  29. hello,, can any one suggest me some solutions for the given scenario?
    i do have a database , i am taking regular back of the database
    the last log back up i have taken at 12. after that there are few changes on the database (some DML operation). next log back is scheduled at 2 . but at 1 o clock i deleted my MDF file(accidentally) deleted… but my ldf file is still intact

    i want to restore my database till the point of failure without taking any tail log backup…

    how can i do that?

  30. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  31. Hi everybody,
    im using the first method:
    USE [master]
    GO
    – Method 1: I use this method
    EXEC sp_attach_single_file_db @dbname=’TestDb’,
    @physname=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLSERVER\MSSQL\DATA\Mydatabase.mdf’
    GO

    but i have this error:
    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file “C:\Exe & Database\RTO DataBase\Database.mdf” failed with the operating system error 3(The system cannot find the path specified.).

    Solution Plz…

  32. Hi, I have a big porblem, I delete one data base from sql server I dont know haow to recover it, please SOME ONE THAT CAN HELP ME HOW..! THANK YOU SO MUCH.

  33. HI!
    i have downloaded a project from internet.. i got to play with it… it need microsoft sql server for db connection…the folder i have downloaded is having 2 db files….one is .mdf and other is .bkp (m sure its for backup).
    the problem is when i open my project in visual studio and try to run one of its form…it give me error of “no database connection”..
    can you help me where to place that .mdf file in sql server to make it work and only .mdf file would be enough.?

  34. Pingback: SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s