locked
Attaching mdf database to SQL 2014 Express RRS feed

  • Question

  • Good day everyone,

    I copied a *.mdf file to c:\program files\Microsoft sqlServer\MSSQL12.SQLEXPRESS\MSSQL\DATA

    I then attached the database.  When I attempted to open the database in management studio I received the following permissions error:Failed to retrieve data for this request. Additional information: Unable to open the physical file "same directory path specified as above". Operating system error5 (access is denied.)" Microsoft SQL Server, Error:5120)

    I then copied the *.mdf file to the same directory path above but one that had ..\\MSSQL11.SQLEXPRESS\MSSQL\DATA and received the same results. : (

    Any ideas? 

    Tuesday, February 17, 2015 8:58 PM

Answers

  • I uninstalled 2014 express and installed the trial version of 2012.  Thank you everyone for being so helpful.  Attaching the sample database worked. 
    Wednesday, February 18, 2015 10:39 PM

All replies

  • Hello

    Make sure that the sql service account has read/write access to the folder where you copied the mdf and ldf files

    Regards


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by bhanu_nz Tuesday, February 17, 2015 9:25 PM
    Tuesday, February 17, 2015 9:11 PM
  • How do I verify what the service account is for SQL? Sorry, I should probably know this.
    Tuesday, February 17, 2015 9:26 PM
  • If you go in to SQL Server Configuration Manager or the Services applet (Services.msc) and find the Database Engine service then you can see the name of the service account running the service.
    Tuesday, February 17, 2015 9:32 PM
  • Hi,

    You Can also use T-SQL as in the link below

    http://sqlandme.com/2013/08/20/sql-service-get-sql-server-service-account-using-t-sql/

    Thanks

    Bhanu

    Tuesday, February 17, 2015 9:39 PM
  • In services I see SQL Server. The logon as is NT Service\MSSQL$SQLEXPRESS.

    When I attempt to lookup users\objects, when modifying the security of the folder, I do not see MSSQL$SQLEXPRESS as an object I can add. 

    Tuesday, February 17, 2015 9:48 PM
  • Have you checked to see if the permissions on the mdf and ldf file are inherited from the parent?  Rather than looking at the permissions of the folder, look at the permissions of the files themselves (after all I'm guessing they're in the same directory as the other databases that are attached to your instance).

    You might then be able to configure the NTFS file permissions so they inherit from their parent if they don't match up.

    Tuesday, February 17, 2015 10:02 PM
  • They are inherited from parent.
    Tuesday, February 17, 2015 10:14 PM
  • This is the service account:

    NT Service\MSSQL$SQLEXPRESS

    When I attempt to add this account in the security settings of the file or the folder it does not find the object.

    Tuesday, February 17, 2015 10:27 PM
  • It looks like the service account has rights to the folder and the file.  It does show up now as MSSQL$SQLEXPRESS.  Is it possible that this adventureworksLT2012_DATA.MDF is not compatible for SQL 2014 Express?  The online tutorial used a trial version of SQL 2012. 
    Tuesday, February 17, 2015 10:42 PM
  • I was just about to ask if you were copying this over from a higher edition of SQL Server (e.g. Standard, Enterprise etc). A trial, or evaluation, copy of SQL Server has the same functionality as Enterprise so you're unlikely to be able to "downgrade" the database. An option for you might be to script the database objects and then export/import the data in to your SQL Express edition.
    • Edited by SQLPhil Tuesday, February 17, 2015 10:57 PM
    Tuesday, February 17, 2015 10:56 PM
  • Hi

    Search for this string NT Service\MSSQL and make sure the location is your computer/machine and select appropriate account/service

    Hope this helps

    Thanks

    Bhanu


    • Edited by bhanu_nz Tuesday, February 17, 2015 11:00 PM
    Tuesday, February 17, 2015 10:59 PM
  • Not sure how to script database objects.  Just for clarification, I'm watching a VTC video and they used a 180 day trial version of SQL.  I downloaded and installed SQL Express 2014 because I wanted to keep it. 
    Tuesday, February 17, 2015 11:01 PM
  • In Object Explorer, right-click the DB and select Generate Scripts and work through the wizard. One other possible option, if you don't mind shelling out a bit, is to buy a Developer edition. It's about $50 and would help you overcome these issues.
    Tuesday, February 17, 2015 11:12 PM
  • Try this ,

    Run SQL Server as administrator , plus edit the permissions of your .mdf file from folder security and assign full control.

    Hope so this will work for you

    • Proposed as answer by Michelle Li Wednesday, February 18, 2015 2:44 PM
    Wednesday, February 18, 2015 8:32 AM
  • If you get the same error, then there still is a permission problem. Make sure that the account has permission to both the mdf and the ldf file. And if you have difficulties assigning permissions using the GUI, then you can use ICACLS.EXE, as in below example (using different file and service name);

    icacls R:\SqlData\p\x.mdf /grant MSSQL$P:(F)


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Michelle Li Wednesday, February 18, 2015 2:42 PM
    Wednesday, February 18, 2015 11:12 AM
  • How big is the MDF file you're trying to attach?  Also when you copied the MDF file, was the originating Database either detached or was the SQL Server Instance stopped?

    Please click "Mark As Answer" if my post helped. Tony C.

    Wednesday, February 18, 2015 12:03 PM
  • I found the service account.  It has full control on the parent folder c:\Microsoft SQL Server.  It is not inherited.  Both the .mdf and .ldf file(s) has MSSQL$SQLEXPRESS user account explicit full control.  Uggghh! 

    Wednesday, February 18, 2015 3:55 PM
  • 8 MB
    Wednesday, February 18, 2015 3:56 PM
  • The file was downloaded from VTC's website.  The recorded lesson is from a SQL 2012 180 day evaluation. It is not SQL Express.  I installed 2014 SQL Express.
    Wednesday, February 18, 2015 3:58 PM
  • When you download the mdf did it completed successfully and its not corrupt? What command you use to attach? could you please paste the errorlog detailed for the same.
    Wednesday, February 18, 2015 5:01 PM
  • I have the security set to the folders and the mdf and log files explicitly.  Could this also be a version incompatibility?  Should I just install the 2012 evaluation copy like the instructor has in the video?  I was hoping to avoid that. 
    Wednesday, February 18, 2015 5:02 PM
  • Is this the log file you need?  Thanks for responding.

    C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log
    errorlog.1

    2015-02-18 09:45:04.33 Server      Microsoft SQL Server 2014 - 12.0.2000.8 (X64)

    Feb 20 2014 20:04:26

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    2015-02-18 09:45:04.33 Server      UTC adjustment: -6:00

    2015-02-18 09:45:04.33 Server      (c) Microsoft Corporation.

    2015-02-18 09:45:04.33 Server      All rights reserved.

    2015-02-18 09:45:04.33 Server      Server process ID is 1332.

    2015-02-18 09:45:04.33 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.

    2015-02-18 09:45:04.33 Server      Authentication mode is WINDOWS-ONLY.

    2015-02-18 09:45:04.33 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG'.

    2015-02-18 09:45:04.33 Server The service account is 'NT Service\MSSQL$SQLEXPRESS'. This is an informational message; no user action is required.

    2015-02-18 09:45:04.33 Server      Registry startup parameters:

    -d C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\master.mdf

    -e C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG

    -l C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\mastlog.ldf

    2015-02-18 09:45:04.33 Server      Command Line Startup Parameters:

    -s "SQLEXPRESS"

    2015-02-18 09:45:04.96 Server      SQL Server detected 1 sockets with 1 cores per socket and 1 logical processors per socket, 1 total logical processors; using 1 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

    2015-02-18 09:45:04.96 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    2015-02-18 09:45:04.96 Server      Detected 1023 MB of RAM. This is an informational message; no user action is required.

    2015-02-18 09:45:04.97 Server      Using conventional memory in the memory manager.

    2015-02-18 09:45:05.33 Server      Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)

    2015-02-18 09:45:07.44 Server      Query Store settings initialized with enabled = 1,

    2015-02-18 09:45:07.48 Server      This instance of SQL Server last reported using a process ID of 2708 at 2/17/2015 5:11:34 PM (local) 2/17/2015 11:11:34 PM (UTC). This is an informational message only; no user action is required.

    2015-02-18 09:45:07.48 Server      The maximum number of dedicated administrator connections for this instance is '1'

    2015-02-18 09:45:07.48 Server      Node configuration: node 0: CPU mask: 0x0000000000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

    2015-02-18 09:45:07.48 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.

    2015-02-18 09:45:07.51 Server      Software Usage Metrics is disabled.

    2015-02-18 09:45:07.55 spid11s     Starting up database 'master'.

    2015-02-18 09:45:07.91 Server      CLR version v4.0.30319 loaded.

    2015-02-18 09:45:08.28 spid11s     SQL Server Audit is starting the audits. This is an informational message. No user action is required.

    2015-02-18 09:45:08.28 spid11s     SQL Server Audit has started the audits. This is an informational message. No user action is required.

    2015-02-18 09:45:08.33 spid11s     SQL Trace ID 1 was started by login "sa".

    2015-02-18 09:45:08.33 spid11s     Server name is 'WIN-AH9KD87VGU5\SQLEXPRESS'. This is an informational message only. No user action is required.

    2015-02-18 09:45:08.51 spid15s     A self-generated certificate was successfully loaded for encryption.

    2015-02-18 09:45:08.56 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].

    2015-02-18 09:45:08.56 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

    2015-02-18 09:45:08.56 spid15s     Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.

    2015-02-18 09:45:08.58 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

    2015-02-18 09:45:08.58 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2015-02-18 09:45:08.58 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/WIN-AH9KD87VGU5:SQLEXPRESS ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

    2015-02-18 09:45:09.12 Logon       Error: 18456, Severity: 14, State: 38.

    2015-02-18 09:45:09.12 Logon       Login failed for user 'NT SERVICE\ReportServer$SQLEXPRESS'. Reason: Failed to open the explicitly specified database 'ReportServer$SQLEXPRESS'. [CLIENT: <local machine>]

    2015-02-18 09:45:09.19 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.

    2015-02-18 09:45:10.83 Logon       Error: 18456, Severity: 14, State: 38.

    2015-02-18 09:45:10.83 Logon       Login failed for user 'NT SERVICE\ReportServer$SQLEXPRESS'. Reason: Failed to open the explicitly specified database 'ReportServer$SQLEXPRESS'. [CLIENT: <local machine>]

    2015-02-18 09:45:11.55 spid16s     A new instance of the full-text filter daemon host process has been successfully started.

    2015-02-18 09:45:11.67 spid11s     Starting up database 'msdb'.

    2015-02-18 09:45:11.67 spid12s     Starting up database 'mssqlsystemresource'.

    2015-02-18 09:45:11.69 spid12s     The resource database build version is 12.00.2000. This is an informational message only. No user action is required.

    2015-02-18 09:45:11.84 spid12s     Starting up database 'model'.

    2015-02-18 09:45:12.01 spid12s     Clearing tempdb database.

    2015-02-18 09:45:13.36 spid12s     Starting up database 'tempdb'.

    2015-02-18 09:45:13.48 spid19s     The Service Broker endpoint is in disabled or stopped state.

    2015-02-18 09:45:13.48 spid19s     The Database Mirroring endpoint is in disabled or stopped state.

    2015-02-18 09:45:13.58 spid11s     Recovery is complete. This is an informational message only. No user action is required.

    2015-02-18 09:45:13.58 spid19s     Service Broker manager has started.

    2015-02-18 09:45:16.25 spid51      Starting up database 'ReportServer$SQLEXPRESS'.

    2015-02-18 09:45:17.31 spid51      Starting up database 'ReportServer$SQLEXPRESSTempDB'.

    2015-02-18 09:45:17.64 spid51      Starting up database 'ReportServer$SQLEXPRESSTempDB'.

    2015-02-18 09:45:18.62 spid53      Starting up database 'ReportServer$SQLEXPRESSTempDB'.

    2015-02-18 09:45:23.53 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.

    2015-02-18 09:45:23.72 spid19s     Service Broker manager has shut down.

    2015-02-18 09:45:23.72 spid19s     Error: 17054, Severity: 16, State: 1.

    2015-02-18 09:45:23.72 spid19s     The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.

    2015-02-18 09:45:27.47 spid11s     .NET Framework runtime has been stopped.

    2015-02-18 09:45:27.48 spid11s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.


    Wednesday, February 18, 2015 5:07 PM
  • Also please giving full rights to everyone to that folder at least will know if its permission issue or not.

    

    Wednesday, February 18, 2015 5:09 PM
  • 1. Please give full access right of adevntureworkslt2012_data.mdf to everyone

    2. Please give full access right the whole folder to everyone 

    3. Open SSMS as administrator and see if attach works

    Please try and let me know how did it go

     
    Wednesday, February 18, 2015 5:22 PM
  • Here is what I see for the following folder:

    C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS

    Everyone has full access but it looks like inherited is enabled.  Should I uncheck inherited?

    Wednesday, February 18, 2015 5:22 PM
  • Leave it enabled
    Wednesday, February 18, 2015 5:29 PM
  • The parent folder c:\program files\Microsoft sql server has everyone and the service account MSSQL$SQLEXPRESS with full control.  The advanced permissions screen has include inheritable permissions from this object's parent as unchecked. 
    Wednesday, February 18, 2015 5:42 PM
  • did you try with opening SSMS "run as administrator" and then attaching
    Wednesday, February 18, 2015 6:05 PM
  • When I attempt to attach the database it lists it's name with recovery pending in parenthesis. Then I go back and check the security on the mdf and ldf, and everyone is mysteriously removed from it.
    Wednesday, February 18, 2015 6:08 PM
  • did you try with opening SSMS "run as administrator" and then attaching

    Yes.
    Wednesday, February 18, 2015 6:08 PM
  • Please enable the inherit option looks like you unchecked it. 
    Wednesday, February 18, 2015 6:19 PM
  • Please open cmd with "run as administrator" and run below command to give rights to everyone

    C:\Windows\system32>icacls "C:\Program Files\Microsoft SQL Server" /grant everyone:(OI)(CI)F

    After this run SSMS "as administrator" to attach the database.

    Let me know the output of the same.

    Wednesday, February 18, 2015 6:30 PM
  • Please open cmd with "run as administrator" and run below command to give rights to everyone

    C:\Windows\system32>icacls "C:\Program Files\Microsoft SQL Server" /grant everyone:(OI)(CI)F

    After this run SSMS "as administrator" to attach the database.

    Let me know the output of the same.

    Same error: 5120 

    I really appreciate everyone trying to help me with this matter. I'm so sorry it isn't working out.  When I run SQL as administrator it still shows my user login credentials and they are greyed out. Not sure if that matters.  I also installed SQL with my user account that has administrative rights with a host VMware machine.

    Wednesday, February 18, 2015 6:38 PM
  • Can you confirm this is what you are doing to open SSMS as administartor?
    Wednesday, February 18, 2015 6:44 PM
  • Can you confirm this is what you are doing to open SSMS as administartor?

    This is correct. 
    Wednesday, February 18, 2015 6:47 PM
  • Please open query window and try with command

    USE [master]
    GO
    CREATE DATABASE [AdventureWorksLT2012] ON 
    ( FILENAME = N'c:\program files\Microsoft sqlServer\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2012_Data.mdf' ),
    ( FILENAME = N'c:\program files\Microsoft sqlServer\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2012_log.ldf' )
     FOR ATTACH
    GO

    do you get the same error?

    Wednesday, February 18, 2015 6:50 PM
  • Please open query window and try with command

    USE [master]
    GO
    CREATE DATABASE [AdventureWorksLT2012] ON 
    ( FILENAME = N'c:\program files\Microsoft sqlServer\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2012_Data.mdf' ),
    ( FILENAME = N'c:\program files\Microsoft sqlServer\MSSQL12.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT2012_log.ldf' )
     FOR ATTACH
    GO

    do you get the same error?

    The command completed successfully but I do not see the database in the list. 
    Wednesday, February 18, 2015 7:43 PM
  • My apologies, I do see the database but I received the same error when attempting to open it.
    Wednesday, February 18, 2015 7:45 PM
  • My apologies, I do see the database but I received the same error when attempting to open it.

    I'm thinking of just uninstalling it. 
    Wednesday, February 18, 2015 7:45 PM
  • I uninstalled 2014 express and installed the trial version of 2012.  Thank you everyone for being so helpful.  Attaching the sample database worked. 
    Wednesday, February 18, 2015 10:39 PM
  • Good day Collector_Edi

    This thread is not readable :-(

    Please don't post each sentence in different response!
    The forum allowed to write more than one sentence on the same message :-)

    * There is an option to edit your message if you want to add something, pls use it if needed. If there is no response to the message then there is no reason to start a new response for the same message.

    This thread look like a chat between 2 people rather then a forum thread.

    THanks,

    * at this point if you still need help, then pleas go over the entire thread and summarize what is still open since people probably will not read it all.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Friday, February 20, 2015 6:47 PM
    Friday, February 20, 2015 6:46 PM
  • Good day Collector_Edi

    This thread is not readable :-(

    Please don't post each sentence in different response!
    The forum allowed to write more than one sentence on the same message :-)

    * There is an option to edit your message if you want to add something, pls use it if needed. If there is no response to the message then there is no reason to start a new response for the same message.

    This thread look like a chat between 2 people rather then a forum thread.

    THanks,

    * at this point if you still need help, then pleas go over the entire thread and summarize what is still open since people probably will not read it all.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]



    My deepest apologies. 
    Tuesday, February 24, 2015 8:14 PM
  • That is OK :-)
    For next time...

    [+1] 

    I glad that you found the solution :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    • Edited by pituachMVP Friday, February 27, 2015 6:55 PM
    Friday, February 27, 2015 6:54 PM