none
Error attaching local mdf file to SSMS

    Question

  • Hi Folks,

    I'm told by my website host support that in order to upload the database portion of my ASP.NET website created with Visual Studio 2017 I need to make a script of the local mdf database in SSMS, then restore it to a new database that I have added to the remote server. My problem is that I am having difficulty attaching the local mdf database to SSMS. I was able to find and connect to my local sqlexpress server, but when attempting to "attach" the database in Object Explorer was unable to "see" my local user directory that contained the mdf database. I then used sql server configuration manager to change the sqlexpress build in account login to "local system", as described in:

    https://stackoverflow.com/questions/19297097/attach-open-mdf-file-database-with-sql-server-management-studio

    I was then able to find and select the local mdf database. However, when I try to "attach", I get the error:"An error occurred when attaching the database(s). Click the hyperlink in the message for details". But there is no hyperlink. 

    Any help would be greatly appreciated!

    Rob

    Friday, May 24, 2019 8:42 PM

Answers

  • Hi Rob,

    I dont have knowledge on Visual Studio so cant say whether that will cause issues.  But the SQL Configuration Manager side, i can help with.  You will have a separate SQL COnfiguration Manager option for each version of SQL you have installed....it will be blank if you're and old one that has been upgraded.  Find the new one and you should see options to set the port.

    Thanks,
    Matt

    • Marked as answer by RobH18 Monday, May 27, 2019 9:10 PM
    Monday, May 27, 2019 4:15 PM

All replies

  • Hi Rob,

    When you say you have the MDF locally, do you mean its on your local machine? To attach an MDF, the file should be on the SQL servers local drive as it will need to have it locally to itself, not on your local machine.  Or have I misunderstand?

    Thanks,

    Matt

    Friday, May 24, 2019 10:12 PM
  • Hi Matt,

    SQL Express comes with Visual Studio 2017, and both are installed on my local desktop computer, as is SSMS. I could not "see" the mdf file from SSMS until I used sql server configuration manager as described. The mdf file is in the AppData folder of the Visual Studio project, and I can now see it in the file picker that comes up in SSMS "attach". But is there more to be done to allow attachment? Or could I copy the database to another location where the copy could be attached? I'm puzzled that there was no information about the nature of the error.

    Rob

    Saturday, May 25, 2019 3:41 AM
  • I do not have direct answer to your problem but it seems like some security thing which does not allows you. What you could do is copy the mdf file to location 

    drive:\Program Files\Microsoft SQL Server\MSSQLn.SQLEXPRESS\MSSQL\Binn 

    n =11,12,13,14 depending on version of sql server.  Close the SSMS and launch it as administrator and hopefully you should be able to see now


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, May 25, 2019 5:06 AM
    Moderator
  • Yeah, Shanky will be right.  When I haven't been able to attach an MDF it has nearly always been permissions related for SQL on that file.  Move the MDF to where Shanky says and that should work :-)
    Saturday, May 25, 2019 7:39 AM
  • Thanks for the suggestion, Shashank. I tried what you suggested. Unfortunately I still get the same uninformative error message as before.

    Rob

    PS: I want to attach an image of what it looks like, but I always get the message: Body text cannot contain images or links until we are able to verify your account. Who knows when that will be?
    • Edited by RobH18 Saturday, May 25, 2019 2:29 PM
    Saturday, May 25, 2019 2:14 PM
  • If you're on SQL Express then you're limited to the DB size.  I dont know what error that would give if you tried to attach it but is it over 10GB in size?

    Thanks,

    Matt

    Saturday, May 25, 2019 4:48 PM
  • Thanks Matt. The DB size is only a little over 3 MB. The website is in development, and the DB only contains test data at this point. BTW, when I connect to SQLEXPRESS in SSMS, I select Windows Authentication, as that is what I use in Visual Studio. Could that be a factor? 

    Rob

    Saturday, May 25, 2019 5:03 PM
  • Hi Rob,

    No Windows auth is fine.  The attachment is done by the SQL service so that needs full access to the MDF file.  Do me a favour though and move the MDF from the binn folder to the DATA one in the same location.

    I thought he asked you to put it in DATA, so if you havent then try that.

    The only other time I can remember having issues attaching MDFs is when it was going to a version older than it was originally on.  But if I remember correctly, the error for that is obvious....maybe not on Express though.  Do you know if the version of SQL this came from was the same year or older than the one you're trying to attach it to?

    Thanks,
    Matt

    Saturday, May 25, 2019 5:17 PM
  • Use T-SQL command to attach the database, so that you get all the error messages:

    CREATE DATABASE mydb ON (NAME = 'mydb', FILENAME = '<path>.mdf'),
    (NAME = 'mydb_log', FILENAME = '<path>.ldf') FOR ATTACH

    mydb should be the name you used in Visual Studio. <path> should be whereever you have the files now. (But preferrably, they should be in the DATA directory.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 25, 2019 6:02 PM
  • Hi Erland,

    Thanks for your suggestion, it has moved things along, but I'm not quite there yet. After running the T-SQL you suggested, I got the message:

    "Msg 1813, Level 16, State 2, Line 1
    Could not open new database 'LabEle3'. CREATE DATABASE is aborted.
    Msg 948, Level 20, State 1, Line 1
    The database 'LabEle3' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported."


    I discovered that my SSMS-visible version of SQLEXPRESS was 10.50.4042, while the Visual Studio Version of SQL Server was 12.00.2000. It did not occur to me that these would be separate instances.

    So: I downloaded and installed the latest version of SQL Server Express (2017), which installed as SQLEXPRESS01, along with the latest version of SSMS (15.0.18118.0, run as administrator from under SQL Server Tools 18 from the Start button). However, on trying to connect to SQLEXPRESS01 in the updated SSMS, I get the error message:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to XPS-DESKTOP\SQLEXPRESS01.

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

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

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

    The system cannot find the file specified

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

    OK
    ------------------------------


    The Help links to an offer to buy a Surface laptop.

    Both SQLEXPRESS (the old version) and SQLEXPRESS01 (the new version) are listed in the Registered Servers. Since the 01 version was just installed, any ideas on why I might not be able to connect to it?

    Rob
    Sunday, May 26, 2019 1:01 AM
  • Hi Rob,

    Firstly, "The Help links to an offer to buy a Surface laptop." - haha

    As you have the second instance of SQL, you will need to either:

    - configure the new instance of SQL Express on a different port number

    - If you're not using the old instance anymore then you can disable that service and use the default port for the new instance (but this will also need to be configured as you're using SQL Express.

    To set the port, you need to go to SQL Server Configuration Manager - this link explains the process

    Then when the port is set, you need to add it to the end of the instance name when you connect via SSMS.  So say you set the new port to 1334 (the default for SQL is 1433) rather than using XPS-DESKTOP\SQLEXPRESS01, you would use XPS-DESKTOP\SQLEXPRESS01,1334.

    *note that the port and instance name are separated by a comma, not a semi colon.

    Let me know if that works.

    Thanks,

    Matt

    Sunday, May 26, 2019 9:45 AM
  • I discovered that my SSMS-visible version of SQLEXPRESS was 10.50.4042, while the Visual Studio Version of SQL Server was 12.00.2000. It did not occur to me that these would be separate instances.

    So: I downloaded and installed the latest version of SQL Server Express (2017), which installed as SQLEXPRESS01,

    Beware that once you have attached the database to this instance, you will not be able to get it back in the instance Visual Studio is using. (Which presumably is localdb.)

    Both SQLEXPRESS (the old version) and SQLEXPRESS01 (the new version) are listed in the Registered Servers. Since the 01 version was just installed, any ideas on why I might not be able to connect to it?

    If I understand it correcly, all this occurs on a single laptop. In that case, the answer is very much likely to be: that instance is not running. Use the SQL Server Configuration Manager to veryify this hypothesis and start the instance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, May 26, 2019 10:08 AM
  • Hi Rob,

    Please follow the below steps to troubleshoot your connection issue.
    1.Make sure your SQL server is running from SSCM.
    2.Check your three protocols. TCP/IP. Shared Memory and Named pipe are enabled.
    3.Check your SQL Server authentication and if your account has connection permission in SQL Server.
    4.Make sure the Firewall is off for a test.
    5.Make sure we can Telnet + IP Address + port number. 
    6.Enable SQL Server Browser Service.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 27, 2019 10:30 AM
  • Hi Matt,

    Thanks for the advice. Following the instructions in your link, when I select SQL Server Network Configuration, I get "There are no items to show in this view"

    I found (from the Start button) that I do have SQL Server 2008, 2008 R2, 2012, and now 2017.

    I'm wondering: would it solve anything to simply uninstall all the SQL Server versions, if that would not compromise my Visual Studio 2017 installation, and then re-install the 2017 version?

    Rob

    Monday, May 27, 2019 3:57 PM
  • Hi Erland,

    Thanks for the advice. In SQL Server Configuration Manager, I find that both instances are running.

    As I also asked of Matt: 

    I found (from the Start button) that I do have SQL Server 2008, 2008 R2, 2012, and now 2017.

    I'm wondering: would it solve anything to simply uninstall all the SQL Server versions, if that would not compromise my Visual Studio 2017 installation, and then re-install the 2017 version?

    Rob

    Monday, May 27, 2019 4:01 PM
  • Hi Rob,

    I dont have knowledge on Visual Studio so cant say whether that will cause issues.  But the SQL Configuration Manager side, i can help with.  You will have a separate SQL COnfiguration Manager option for each version of SQL you have installed....it will be blank if you're and old one that has been upgraded.  Find the new one and you should see options to set the port.

    Thanks,
    Matt

    • Marked as answer by RobH18 Monday, May 27, 2019 9:10 PM
    Monday, May 27, 2019 4:15 PM
  • Thanks for the advice. In SQL Server Configuration Manager, I find that both instances are running.

    How many instances do you see?

    If both instances are running, this means that you are not giving the correct instance name.

    I found (from the Start button) that I do have SQL Server 2008, 2008 R2, 2012, and now 2017.

    Not really. That only means that you have some component from each version. To tell which instances you have, you need to look in SQL Server Configuration Manager.

    I'm wondering: would it solve anything to simply uninstall all the SQL Server versions, if that would not compromise my Visual Studio 2017 installation, and then re-install the 2017 version?

    Uninstalling is not going to help your connection problem. If you uninstall SQL Server components that were added by the VS installation, VS will of course suffer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, May 27, 2019 9:09 PM