2

enter image description hereI had Express edition installed of SQL Server 2016. I needed to use SQL Agent Jobs. For that purpose I installed a patch of SQL Server 2016 enterprise edition but it didn't worked. After that I installed SQL Server 2017 Developer edition which showed me the SQL Agent Jobs node (which i wanted). After installation i was able to see my databases in both servers 16 & 17 connected locally. Today when I logged into my system and connected the instance of the server I'm not able to see my databases. When i try to attach the data files located at:

C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA

it does not show the file in the popup to attach it. Although in the file explorer I can see the file. For reference I have added an image of the list of databases, i cannot find my databases in it. Any help regarding it would be thankful. Screenshot from both SCM's. enter image description here

Harry .Naeem
  • 1,245
  • 3
  • 20
  • 33
  • 1
    what is the output of select * from sys.databases? is your db there? – Anton Feb 08 '18 at 05:28
  • @Anton I have updated my question, ran your query, it is not there. – Harry .Naeem Feb 08 '18 at 05:34
  • 1
    it's likely you have multiple instances now, check it in SQL Configuration Manager – Anton Feb 08 '18 at 05:36
  • In SQL Configuration Manager 2016 -> SQL Server Services, I can see SQL Server (SQLEXPRESS) State Running , SQL Server Agent State Stopped, SQL Server Browser State stopped – Harry .Naeem Feb 08 '18 at 05:41
  • SQL CM 2016 may not see SQL 2017 services. Try to check 2017 CM, or go directly to the services. From your screenshot it's clear that you're connected to 2017 server, but your databases are still on 2016 server (Express) – Anton Feb 08 '18 at 05:43
  • I have checked 2017 CM, and stopped the service that was being shown in the 2016 CM. Now how to get those databases here? – Harry .Naeem Feb 08 '18 at 05:47
  • So can you confirm you had two instances when you opened 2017 CM? If you want to move databases from SQL 2016 to 2017, you need to detach it from 2016 first and then reattach in 2017. 2017 service account should have permissions for 2016 folder. Alternatively just connect to 2016, make a backup, and restore DB to 2017. – Anton Feb 08 '18 at 05:50
  • I'm off in 10 mins – Anton Feb 08 '18 at 05:51
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/164725/discussion-between-harry-naeem-and-anton). – Harry .Naeem Feb 08 '18 at 05:55

1 Answers1

4

You installed second instance (2017). You need to connect to 2016 instance (using /instance_name) and then make a backup, and then restore backup (+transfer logins) on 2017 server, and finally uninstall 2016. Or detach files on SQL 2016 instance and reattach on 2017 + uninstall.

Alternatively you need to choose in-place upgrade 2016-> 2017 instead of installing a new instance.

Anton
  • 2,846
  • 1
  • 10
  • 15