Installing Multiple Versions of SQL Server Side-by-Side on a Development Machine

You cannot restore a SQL Server backup file on an older version of SQL Server.

This was a problem recently for me because I needed to distribute a .bak file that would be compatible with SQL Server 2014 and later.  I had a copy of SQL Server 2019 Developer's Edition on my development machine.  When I tried restoring a database backup on an instance of SQL Server 2014, I got the following error message:

Luckily, you can install multiple versions of SQL Server side by side on the same machine.

Installing Multiple Instances of SQL Server

Installing additional instances of SQL Server when you already have one installed is not that different than installing the first instance of SQL Server.  

Most versions of SQL Server will coexist peacefully side by side.  Here's a link to a table with the side-by-side compatibility (available as of 2023-04-11).

I won't go into detail on how to install the second instance of SQL Server.  There are plenty of resources on the web that will step you through that.  However, I will highlight some of the specific options I overrode that I think made sense for a scenario such as this.  I should also note that I chose to install SQL Server Express 2014 because I already have a Developer's Edition of SQL Server 2019.  The Express version has everything I need for the few situations where I will need to use SQL Server 2014 (mainly for generating backwards-compatible .bak files).  

• Instance Configuration
  • Named Instance: SQLEXPRESS2014 (replace "2014" with the relevant SQL Server version)
• Server Configuration
  • SQL Server Database Engine Startup Type: Manual
  • SQL Server Browser Startup Type: Automatic

I included the version number in the instance name to make it easier to work with for scripting and identification in the Services list.  I set the startup type to Manual to avoid having the instance consume resources when not in use (which will be the vast majority of the time).

Starting and Stopping the Service via the Command Line

To start the instance via the command line, we can use the net start command. You need to use the service name for the instance, which is MSSQL followed by a dollar sign ($) followed by the instance's name (see above).

net start MSSQL$SQLEXPRESS2014

To stop the service, you use the net stop command.

net stop MSSQL$SQLEXPRESS2014

If you receive an "Access is denied" error message, you will either need to run the command as an Admin (inconvenient) or grant your account the right to start and stop the service.  

Granting Access to Start/Pause/Stop a Service

To do this within the spirit of least privileged access, we want to limit the authorization to a single service.  

This article shows several approaches, but I found most of them impractical for one reason or another:

• sc.exe: the SDDL format has a confusing and complex format
• SubInACL: this tool is no longer officially available from Microsoft; it is also not included in the current version of SysInternals
• PowerShell: the PowerShellAccessControl module is no longer available
• Security Templates: lots of manual steps involved
• Group Policy Objects: does not apply because my computer is not joined to a domain

The Process Explorer approach worked really well but it was a bit sparse on details.

Using Process Explorer to Grant Rights to Individual Windows Services

Here are the steps I followed to grant my non-admin user account permission to start and stop the SQL Server Express 2014 service without requiring entering my credentials every time:

1. Start the MSSQL$SQLEXPRESS2014 service via services.msc
2. Open Process Explorer
3. Double-click on the sqlservr.exe process with "SQLEXPRESS2014" in the command line
4. Click on the Services tab
5. Click the [Permissions] button
6. Click [Add...] button
7. Enter my non-admin user account "Mike" then click [Check Names] then click [OK]
8. Choose the new entry and click [Advanced]
9. Choose the new entry and click [Edit]
10. Click "Show advanced permissions"
11. Check the following boxes:
    [√] Pause/Resume
    [√] Start
    [√] Stop
12. Keep clicking [OK] until all boxes are closed

Before and After Permissions Update

Here's what the command line looked like before and after I updated the permissions using the steps above: