Sql-server – All instances upgrade from Command Prompt

command linesql serversql-server-2016upgrade

I want to create a cmd script to upgrade sql servers in the organization(mostly 2014) to 2016. I've read Microsoft documentation
Install SQL Server 2016 from the Command Prompt regarding this issue.
The problem is that there is no option written there to upgrade all server instances like there is in installing updates: Installing Updates from the Command Prompt using /allinstances.
Is there any way to auto upgrade all instances on the server using one command line?

Best Answer

You can first download the SQL server 20XX edition of your needs here. Then you can download and extract the local content media on your machine. After that you should see in the downloaded folder the SETUP.EXE file Then you can open the command prompt and type (By considering your sql standard instance name is MSSQLSERVER):

C:\Path_to_file\SETUP.EXE /QS /IACCEPTSQLSERVERLICENSETERMS /ACTION=UPGRADE /INSTANCENAME=MSSQLSERVER

When you connect to your database, you should see the new version installed . In my case I upgraded from SQL server 2017 to SQL server 2019. Old Maj version was 14.XX, now the new Maj version is 15.XX. See below.

SQL server 2019 version

And there are plenty of cool explanations on this page

EDIT: I think its not possible to upgrade all instances at one with the allinstances parameter, because not allowed (Tried it). Here is what the command line says:

 ALLINSTANCES                 Specifies that all instances are to be included
                              in the Setup operation. This parameter is
                              supported only when applying a patch.

Bu you can loop with powershell on all your instances with the above command. If you dont have 1 million instances, it should be fine.

Related Question