Background:
The intent of this post is to discuss how to speed up the staging of an existing SQL Server either when moving to new hardware but keeping the same data and SQL Edition OR when you need to downgrade the edition of SQL Server installed incase you staged Enterprise when you meant to use Standard or Developer for the instance.
Setup and Code:
The following is a exert from the article published by Jonathan Kehayias and having used the steps 2-3 times myself for various situations I am confident in the use.
- Take a good backup of all of your databases (system and user).
- Run SELECT @@VERSION and note the specific build number of SQL Server that you are currently on.
- Shut down your existing instance of SQL Server.
- Copy the master, model, and msdb database files (both mdf and ldf), don’t move them copy them, from the current location to a new folder that you mark as readonly.
- If downgrading the Edition uninstall SQL Server from the system and reboot the server, otherwise go to Step 6.
- If you are cloning via SAN , have your SAN Admin copy the data from the old server and present the drives with the same drivers letters once the copy is complete.
- Install SQL Server Standard Edition ( if downgrading editions) Or if reinstalling SQL Server on a server with new hardware, install the same version you had previous to the hardware change. I have done this before to reinstall shared components and get the registry values setup and defaults for SQL Server directories set. Make sure your paths and drive letters match the old configuration.
- Apply the necessary Service Pack and/or Cumulative Updates to bring the instance up to your previous build number.
- Shutdown SQL Server.
- Copy the master, model, and msdb database files (both mdf and ldf) from the folder you saved them in to the correct location for the new install and remove the readonly flag from the files, and change the file ACL’s so that the SQL Service account has Full Control over the files.
- Open Surface Area Configuration and set protocols, aliases and start up parameters for the services matching the old configuration.
- Startup SQL Server and if you did it correctly it will startup and be exactly where you were before you made any changes, with all of your user databases online and you should be ready to let applications connect and resume operations.
Once you have the server up and running, I suggest you run a quick CheckDB on your system databases and ensure all the databases are recovered and online correctly. Lastly, it is good to perform a backup of your System Databases via SQL Agent Job, this will test your directory ACLs set above and also that your SQL Agent is functioning properly.
Note: Having copied your previous master and msdb files back to the instance, this will re-populate all users, permissions and Agent Jobs, thus speeding the time to recover and eliminate the guess work!
Resolution:
This article details a couple use cases where by using your existing system database data files you can change the Edition of SQL Server or change SQL Server hardware on the host and speed up the time needed to recover the instance.
Information on Terms:
Edition – This refers to Microsoft SQL Server edition ( Express, Developer, Standard, Enterprise) of the database software , downgrading between Version is not supported. ( ex: 2016 Standard to 2014 Standard)
Note: The installer can be used to upgrade an instance from a lesser Edition ( ex: Standard Edition to Enterprise Edition)
Subject Links:
https://www.sqlskills.com/blogs/jonathan/downgrading-sql-server-editions/