I have a SQL Server 2012 Express database in a C# WinForms application and would like to upgrade it to SQL Server 2016 Express. I also have both SSMS 2012 Express and SSMS 2016 Express i.e.(Microsoft SQL Server Management Studio-18.8) Can I use SSMS 2016 Express to upgrade the existing database, or do I need to install a separate instance of SQL Server 2016 Express? In other words, would attempting to use SSMS 2016 Express to attach to the database, perform an upgrade to 2016? Or, what steps do I need to take to upgrade the database?

No - SSMS is a client tool and does not upgrade SQL Server. To upgrade the database engine, you need to download SQL Server 2016 Express Edition and run the setup - which will then prompt you to upgrade the existing instance. But - that version is already 5 years old and you should be considering SQL Server 2019 Express ( https://www.microsoft.com/en-us/sql-server/sql-server-downloads ).

Hi @Richard Arnold ， > Can I use SSMS 2016 Express to upgrade the existing database, or do I need to install a separate instance of SQL Server 2016 Express? No, you can’t. SSMS is a tool to manage SQL server instance, you can not use SSMS to upgrade SQL server instance. > what steps do I need to take to upgrade the database? According to MS document , to upgrade SQL server 2012 Express to SQL 2016, you need to apply SP2 for SQL server 2012 Express at least. Then download SQL server 2016 Express from here and run the setup file as administrator to upgrade SQL 2012 SP2 to SQL 2016 . Please follow the steps from the blog or MS document to upgrade SQL server instance. SQL server 2019 Express is the latest version, you can also upgrade SQL 2012 Express to SQL 2019 Express. But according to MS document , you need to apply SP4 for SQL 2012 at least. Then download SQL 2019 Express from here and run the setup file as administrator to upgrade SQL 2012 SP4 express to SQL 2019 Express. SSMS 18.8 is the latest general availability (GA) version of SSMS. Suggest you using it. If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Additional see Upgrade SQL Server for more details about the upgrade process.

Can I use SSMS 2016 Express to upgrade SQL Server 2012 Express

Accepted answer

Jeffrey Williams 1,886 Reputation points

2021-03-25T21:18:51.433+00:00

No - SSMS is a client tool and does not upgrade SQL Server. To upgrade the database engine, you need to download SQL Server 2016 Express Edition and run the setup - which will then prompt you to upgrade the existing instance.

But - that version is already 5 years old and you should be considering SQL Server 2019 Express (https://www.microsoft.com/en-us/sql-server/sql-server-downloads).
Please sign in to rate this answer.

1 person found this answer helpful.
Richard Arnold 181 Reputation points

2021-03-26T16:53:15.333+00:00

@Jeffrey Williams I took your advice, downloaded and installed SQL Server 2019 Express. My sql 2012 already had SP4 installed.
It turned out that I already had SSMS 18.8 installed from a few days ago. I thought it was version 2016. Now I understand about 18.8.

After the install, I was able to logon to SSMS 18.8, select my database and could see that the data had carried over from version 2012, so I should be good-to-go.

One last question: As my C# App uses a SQL instance of SQLEXPRESS, I noticed that the install created an instance of SQLEXPRESS01.
Will this present a problem in connecting to it via my C# App?

Jeffrey Williams 1,886 Reputation points

2021-03-26T18:49:32.363+00:00

If the name of the instance has changed - then your connection string in your application will have to be updated with the new name. An upgrade should not have created a new instance though - so I am a bit confused as to why the name is different.

Richard Arnold 181 Reputation points

2021-03-26T20:57:31.713+00:00

@Jeffrey Williams The install file I used is named SQL2019-SSEI-Expr.exe. I wanted to confirm that SQL had in fact been upgraded.
I found a suggestion to query it using the following syntax: DBCC CHECKPRIMARYFILE ({'FileName'} [, opt = {0|1|2|3}])

However, this provided the value 706, which, from what I read online, is still a 2012 version. I am somewhat confused as to why the SQL2019-SSEI-Expr.exe executable installed, but may not have actually upgraded the database. However, I can connect to the DB using SSMS 18.

Do you have any suggestions on how to verify the Db is upgraded, or what steps I still need to take?

Jeffrey Williams 1,886 Reputation points

2021-03-26T21:17:37.65+00:00

Check SQL configuration manager and verify there is only one instance installed

Richard Arnold 181 Reputation points

2021-03-27T13:35:11.497+00:00

@Jeffrey Williams Using SQL Server 2019 Configuration Manager, I took a screen shot of what it showed.
From the image shown, it displays more than one instance of the server. Also, it keeps showing a version 11, instead of version 15.

Perhaps from the snapshot you will be able to tell more than I can interpret. However, I've downloaded and installed SQL upgrades in the past, i.e. 2008 - 2012, and was presented with the options to do an inplace install or an upgrade. However, when I ran the file SQL2019-SSEI-Expr.exe, I was not presented with any option to upgrade. This is what leads me to wonder if the 2012 database was never upgraded.

Jeffrey Williams 1,886 Reputation points

2021-03-27T15:22:48.103+00:00

You need to check the installed services - click on SQL Server Services and validate what services are installed and running. It does look like multiple instances have been installed here.

When you use SSMS to connect to an instance - if you are using this .\SQLEXPRESS you are connecting to the original 2012 instance. Try connecting to .\SQLEXPRESS01 and .\SQLEXPRESS02 to see if you can connect to those instances.

I am guessing that you will be able to connect to those instances - but won't find your database. That is easy to fix though - login to .\SQLEXPRESS and back up the user database(s) - then login to .\SQLEXPRESS01 and restore the database(s). Script out any logins from .\SQLEXPRESS and create them on .\SQLEXPRESS01 - and try to connect your application to the new instance.

Richard Arnold 181 Reputation points

2021-03-27T17:35:18.73+00:00

@Jeffrey Williams I backed up the DB from SQLEXPRESS, where my backup location I routinely use is C:\Backups\MyBackups. However, I wasn't able to restore it from SQLEXPRESS01. So I did an Attach to the DB and performed another backup, which SQL Server stored in a new location, i.e. C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\Backup.

However, the DATA directory in this path does not yet have the actual .mdf database in it.
The directory path I normally connect to the DB from is C:\AppDB\xx.mdf.
Is the .mdf file that is in that location, now a 2019 version file, or will it still be the 2012 version?

When I modify the connection string in my App, I need to be sure that I'll be connecting to a 2019 DB.
As C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\DATA does not yet reflect the existence of the .mdf, how will I know for sure which version I'll be connecting to?

Richard Arnold 181 Reputation points

2021-03-27T20:13:55.957+00:00

@Jeffrey Williams OK, I'm starting over and trying to accomplish the steps you outlined. I brought over a clean copy of my DB from a 2nd system I have. I replaced the old DB with the clean copy. I then logged into SQLEXPRESS and successfully backed up the user database. I then logged into SQLEXPRESS01 and successfully restored the database.

However, I need some assistance in the step: "Script out any logins from .\SQLEXPRESS and create them on .\SQLEXPRESS01 and try to connect application to the new instance. How do I 'Script out the logins'?

Richard Arnold 181 Reputation points

2021-03-27T20:20:16.087+00:00

@Jeffrey Williams The reason I asked the last question, is because other than the backup file, no DB shows up in C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\DATA. So I'm not sure how my App can connect to the DB.

Do I need to copy the database from the previous location to the above location? Or can I assume that my working directory for the application, i.e. C:\AppDB\xx.mdf now contains a 2019 version of the database?

Jeffrey Williams 1,886 Reputation points

2021-03-29T15:39:48.117+00:00

Sorry I didn't respond sooner - out for the weekend.

There are a lot of questions here that need some clarification. First - to copy logins you can download and use these PS tools: https://dbatools.io/

Second: Not sure how you 'copied' the database from the other system. If you copied the mdf/ldf files from the other system and overwrote the existing files - that is not a good method. You need to use backup/restore to accomplish this as it is much safer than copying files.

Third: when restoring the database using SSMS - the dialog boxes will tell you where the new mdf/ldf files are being created. You don't need to 'connect' to the database using the file system, you connect to the SQL instance.

Richard Arnold 181 Reputation points

2021-03-29T20:38:34.49+00:00

@Jeffrey Williams To answer the 2nd question. Because my C# App is deployed via ClickOnce, that method normally creates an obfuscated install. I wanted to incorporate an internal method of performing a BACKUP and RESTORE of the database that the end-user could perform and access.
I created a backup directory of C:\Backups\MyBackups. I then had the assistance of a gentleman from your SQL Server support, who instructed me on writing the Backup and Restore commands within my App. This has been working successfully for several years now.
My work directory for the DB is C:\AppDB\xxx.mdf, xxx_log.ldf. Using SSMS 18, I went to Tasks > Back Up, pointed SSMS to C:\AppDB\xxx.mdf and this backed up the DB to C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\Backup.

Jeffrey Williams 1,886 Reputation points

2021-03-29T20:53:57.453+00:00

Sorry - even more confused now. You backup a database - not the files...but you seem to have 'pointed' SSMS to the mdf file? Not sure how that is done...

At this point - I really don't know if you upgraded the instance, created a new instance - or what. If you can login to SSMS and connect to .\SQLEXPRESS01 - then in the Object Explorer open the databases folder. If you see your database in that folder then it exists in that instance.

Richard Arnold 181 Reputation points

2021-03-29T20:57:08.237+00:00

@Jeffrey Williams Using SSMS18, I can successfully connect to .\SQLEXPRESS01 and can see and query the database tables. However, although I changed the connectionString to reflect .\SQLEXPRESS01, now when I run my App and attempt to perform a backup, it throws an error, which I have attached for you to see.

What do I need to do, in order to have my application's backup and restore functionality work as before?

Richard Arnold 181 Reputation points

2021-03-29T21:14:29.113+00:00

@Jeffrey Williams OK, I just solved the problem. Earlier when I changed the connectionString properties to reflect .\SQLEXPRESS01, the section of code that calls my Backup and Restore still referenced .\SQLEXPRESS, rather than .\SQLEXPRESS01. After changing it here, it now works successfully. Sorry for all the confusion. Thanks again for your help! The only thing that threw me, was that previously, the actual .mdf and log file showed up in the DATA directory in the 2012 version (MSSQL11.SQLEXPRESS), but doesn't in version 15 (C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\DATA).

Perhaps Microsoft has made a change somehow.

Jeffrey Williams 1,886 Reputation points

2021-03-29T21:32:18.347+00:00

By chance - does your connection string use 'AttachDatabase' as part of the string? Can you provide the code you use to backup the database?

Richard Arnold 181 Reputation points

2021-03-29T21:42:17.203+00:00

@Jeffrey Williams Yes, my connection string uses 'AttachDatabase' as part of the string. I've attached a image of the commands I use, which I access through a WinForms dialog for the end-user to both backup and restore, which works well.
Sign in to comment

Can I use SSMS 2016 Express to upgrade SQL Server 2012 Express

2 additional answers