none
Cannot attach an mdf file to SQL

Answers

  • Is the "C:\TEMP\PRESCRIPTIONCONTOSO_log.LDF" visable on the disk? operating system error 2 basically says I cannot find that file at that location. Every database is made up of at least 2 files, the MDF (data file) and the LDF (transaction log) file. SQL does write ahead logging therefore without the log file the database cannot be brought online.

    Do you have both files, are they located in C:\temp and do you have permissions for both file as per my previous post?


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by MichelRub Tuesday, February 14, 2012 10:58 PM
    Monday, February 13, 2012 11:21 PM

All replies

  • Ensure SQL has full access to the C:\Temp\ folder and PRESCRIPTIONCONTOSO.MDF and LDF files and see if that helps.
    • Proposed as answer by Sean Massey Thursday, February 9, 2012 9:24 PM
    Thursday, February 9, 2012 9:03 PM
  • Hi,

    As Kevin says OS error 5 is an access denied. What account is running your SQL Server service? You need to make sure that account has access to the path and file as Kevin already stated.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, February 9, 2012 9:23 PM
  • Thnaks for your reply.  Can you tell me how can i check if I have access to the path C:\Temp and the correpsonding file???

    Thanks again

    Michel

    Friday, February 10, 2012 3:46 PM
  • Check what account is running your SQL server service via Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. Select SQL Server Services and check the Logon as information for SQL Server (Instance Name).

    Then open Windows Explorer > browse to the C:\ drive > right click Temp > Properties > Security > Edit > Add > Enter the user that you found running the service > Select Allow Full Control > click OK.

    Once this is done browse to the MDF files under C:\Temp and right click them > Properties > Security > Select the user running SQL and confirm that Full Control is checked for that user.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Friday, February 10, 2012 9:23 PM
  • I did as you sugested and received the following error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Attach database failed for Server 'DIRECCION3\SQLEXPRESS'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Unable to open the physical file "C:\TEMP\PRESCRIPTIONCONTOSO_log.LDF". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5120)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5500&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    Thanks for your help

    Michel Rubinstein

    Monday, February 13, 2012 5:50 PM
  • Is the "C:\TEMP\PRESCRIPTIONCONTOSO_log.LDF" visable on the disk? operating system error 2 basically says I cannot find that file at that location. Every database is made up of at least 2 files, the MDF (data file) and the LDF (transaction log) file. SQL does write ahead logging therefore without the log file the database cannot be brought online.

    Do you have both files, are they located in C:\temp and do you have permissions for both file as per my previous post?


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by MichelRub Tuesday, February 14, 2012 10:58 PM
    Monday, February 13, 2012 11:21 PM
  • The LDF file is not in my disk on C:\Temp.  I downloaded the files for studying purposes, and the LDF is not in the downloaded files.  But I did copy another files (both the MDF & LDF) and attched succesfully the files to my SQL.

    Thank you for your help

    Michel Rubinstein

    Tuesday, February 14, 2012 10:58 PM
  • Hi Michel

    Can you tell us where you found the LDF?

    (FRUSTRATED!)

    Thursday, April 26, 2012 6:35 PM
  • One of my sql 2008 production database 8mdf files, can a database has multiple mdf files? what i thought was there could be only one mdf file & multiple ndf files.

    Thursday, May 10, 2012 4:47 PM
  • Hi,

    Yes. You can name the secondary data files whatever extension you want. It's best practice to use NDF but SQL does not force it as you can see in the below example.

    USE [master]
    GO
    CREATE DATABASE [testmdf] ON  PRIMARY 
    ( NAME = N'testmdf', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\testmdf.mdf' , SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'testmdf_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\testmdf_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    
    USE [master]
    GO
    ALTER DATABASE [testmdf] ADD FILE ( NAME = N'test2', 
    FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\test2.mdf' , 
    SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
    GO
    
    use testmdf
    go
    select * from sys.database_files
    
    


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Friday, May 11, 2012 1:18 PM