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

Question

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,

-

Answer 1

Hi @Nimal ,
I'm not sure whetehr this soultion is connected with location or being influenced by the location.
See this link: https://www.sqlnethub.com/blog/how-to-connect-to-sql-server-from-visual-c-plus-plus/
And I suggest you to open a new case in our platform, which will make your case be seen by more people.
Wish you good luck !

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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