locked
Version info reports 2017 but creates new Db in path of older version? RRS feed

  • Question

  • I have several previous versions of SQL installed.  Probably time to clean them all out and reinstall the single version I need on my workstation.  Regardless, I was curious why this situation is occurring when trying to create a new Db on my local instance.

    SELECT @@VERSION
    Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: ) (Hypervisor)

    However, when I create a new db, it wants to use the file paths in the root of a previous verison

    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

    Why ?

    Looking at the path of the only instance running as reported by the SQLServer MMC, I see
    C:\Program Files\Microsoft SQL Server\MSSQL10.MyLocalInstance\MSSQL\Binn\sqlservr.exe

    Seems like I have a 2008 still running, so then why does @@VERSION report 2017 ?

    I think I see part of the problem.  This article helped
    https://blog.sqlauthority.com/2016/05/30/sql-server-configuration-manager-mmc-not-create-snap-snap-might-not-installed-correctly/

    Once I loaded the SQL 2017 Msc, I could see the 2017 instance I had running.  So that leaves the question as to why I cant create a new db in its path


    • Edited by shiftbit Tuesday, October 22, 2019 1:54 PM sdfsdf
    Tuesday, October 22, 2019 1:48 PM

Answers

  • However, when I create a new db, it wants to use the file paths in the root of a previous verison

    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

    Why ?

    That's quite normal when you perform an inplace migration then the used folders remains as they are created/used by the previous version.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Tuesday, October 22, 2019 2:08 PM
    • Proposed as answer by Tom Phillips Tuesday, October 22, 2019 2:51 PM
    • Marked as answer by shiftbit Wednesday, October 23, 2019 6:24 PM
    Tuesday, October 22, 2019 2:07 PM

All replies

  • However, when I create a new db, it wants to use the file paths in the root of a previous verison

    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

    Why ?

    That's quite normal when you perform an inplace migration then the used folders remains as they are created/used by the previous version.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Tuesday, October 22, 2019 2:08 PM
    • Proposed as answer by Tom Phillips Tuesday, October 22, 2019 2:51 PM
    • Marked as answer by shiftbit Wednesday, October 23, 2019 6:24 PM
    Tuesday, October 22, 2019 2:07 PM
  • I think I figured it out.  I went into the instance properties / database settings.  There were the paths for the default database locations.  They were listed in the former instance path(s).  I updated those.  then when I attempted to create a new db, I got a permission error.  Hmmm, so after investigating that I realized I needed to give the MSSQLSERVER local system account full control of the 'DATA' folder.  After that,all was well again.

    In short, unless you have to have multiple instances on your workstation.  Just wipe out the others and use only 1 :)  Makes life much easier.

    Tuesday, October 22, 2019 2:20 PM
  • Hi shiftbit,

     

    I am glad to know that your problem has been solved. In order to close this thread, please  mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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

    Wednesday, October 23, 2019 9:17 AM