After SQL server upgrade from 2012 to 2019, DB from 2012's location is still accessed

Nimal 1 Reputation point
2022-09-28T03:29:40.913+00:00

I am using SQL server Express 2012 in MS VC++ code. I use the below connection string for opening the DB.

  • The 2012 DB is located at the folder 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.DTC\MSSQL\DATA'.
  • After upgrading to MS SQL 2019 Express, it is supposed to access the DB at location 'C:\Program Files\Microsoft SQL Server\MSSQL15.DTC\MSSQL\DATA'
  • But MS SQL 2019 used in out code still loads and uses the DB at 2012's location only, instead of 2019's location.
  • This incorrect location access issue happens only with the upgraded DB.
  • The driver I use in openEx connection string is '"DRIVER={SQL Server Native Client 11.0};SERVER=%s%s;MARS_Connection=yes;%s"'
  • I have tried the following ways for 2012-2019 SQL express DB upgrade
  • *used Backup/restore by SSMS 18
  • through the upgrade wizard in 2019.
  • Data migration using DMA*

Please suggest a way to access the upgraded DB in VC++. Any help is sincerely appreciated.

Thanks,

-

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,713 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
494 questions
C++
C++
A high-level, general-purpose programming language, created as an extension of the C programming language, that has object-oriented, generic, and functional features in addition to facilities for low-level memory manipulation.
3,527 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. PandaPan-MSFT 1,901 Reputation points
    2022-09-28T05:46:01.26+00:00

    Hi @Nimal ,
    How about trying changing the loctaion in the SSMS.
    You can choose your database and right click----choose properties----database settings. See whether the laoctaion is right:
    245374-image.png

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

    0 comments
  2. Bjoern Peters 8,781 Reputation points
    2022-09-28T07:15:42.533+00:00

    In a case of an Inplace-Upgrades, only the db-engine was upgraded, which results in different installation paths.

    C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.DTC\MSSQL\DATA
    C:\Program Files\Microsoft SQL Server\MSSQL15.DTC\MSSQL\DATA

    BUT the location of the database will not be touched during this in-place upgrade! So your database will stay at its old location!
    AFTER that upgrade, it is recommended to detach the database, move the files to the new location and re-attach those files.

    Please check via SSMS where your database files are located.
    Database -> Properties -> Files

    245370-image.png

    0 comments
  3. Olaf Helper 40,816 Reputation points
    2022-09-28T08:36:36.337+00:00

    it is supposed to access the DB at location 'C:\Program Files\Microsoft SQL Server\MSSQL15.DTC\MSSQL\DATA'

    No, with an in-place migration all instance related folders remains as they are, nothing changes or gets moved around.

    0 comments
  4. PandaPan-MSFT 1,901 Reputation points
    2022-09-29T07:07:30.337+00:00

    Hi @Nimal ,
    We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

  5. Nimal 1 Reputation point
    2022-11-03T10:38:27.75+00:00

    Thank you @Bjoern Peters , @Olaf Helper , @PandaPan-MSFT .
    Detach, copy mdf and ldf file into SQL's location for DATA. attach and alter user and login worked
    I tried these in MSSMS18.
    The same does not work on scripts. Tried with drop and recreate user/login also did not help. Hence this delayed reply.
    Thanks again to all

    0 comments