Clone A SQL Server Software Installation

Cloning is perhaps not the right term to use here but let us pretend that it is and keep going. Let us say that you want to install SQL Server on another host say NewHost but you want it to have the same settings as another host/instance on say ModelHost. In fact, let us say that these are almost identical hosts with similar drive locations and such and you are positive that you want the exact setup on both.

The hard way is to look at what features ModelHost has and try to click through the installation wizard with the correct options selected/input.

Easy way to clone an installation

Fortunately, there is an easier way to clone an installation. Even if you installed manually using the installation wizard, SQL Server still generates a configuration file with all the settings used (except passwords) for the installation. We can simply use that file from the ModelHost to drive the installation on NewHost

Here are the simple steps

1. On ModelHost, locate the configuration file generated: Depending on the version you installed, the location would be somewhere like the path shown below (change the 130 to match your SQL Server version) –

   C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20171025_174755\ConfigurationFile.ini

2. Copy the file over to NewHost – say at D:\
3. Make the following changes to the ConfigurationFile.ini
   1. Remove quotes in INSTANCEDIR=”D:\” (for example):INSTANCEDIR=D:\
   2. Comment this line by adding a semi-colon at the beginning;UIMODE=”Normal”
   3. Add a line withIACCEPTSQLSERVERLICENSETERMS=”True”
   4. Change the instance name if it will be different from ModelHost
4. In NotePad, comeup with the command line you will use. Substitute the passwords with the right values. Mine looked something like this: \\networkshare\sql2016\setup.exe /ConfigurationFile=SQLInstallConfig.ini /SQLSVCPASSWORD=”SOMETHING” /AGTSVCPASSWORD=”SOMETHING” /SAPWD=”SOMETHING”
5. If you have additional features like SSRS etc, you would have to specify the passwords for those service accounts too. Basically, all the accounts that need a password have to have their password specified in the command-line above
6. You are ready to run now in the command-line after CD’ing to the location of the configuration file
   1. cd d:\
   2. Run the command that you have ready in NotePad
7. You would still have to install the Service Pack yourself at this point
8. Security/logins/specific objects you setup after the original installation on ModelHost will not be in NewHost. For logins and other objects you want to bring over, you can use dbatools.io PowerShell module. You can even bring over complete databases. In fact you can copy every imaginable type of object you have on ModelHost using dbatools.

When it is completes, you have an cloned installation!

Troubleshooting

When running an installation from the command-line, you may run into issues. The logs are your best friend. Please check the contents of the log to know more about the errors you run into. You will find information about how to do this in my earlier blog post here:
SQL Server – Resolve “The /IAcceptSQLServerLicenseTerms command line parameter is missing or has not been set to true.”

I personally ran into this error – “The RPC server is unavailable”. Very helpful message there! Anyway, I was able to find and fix the problem in the new host using the link below. These are not your “run of the mill” checks but are very handy if you run into this error:

https://deibymarcos.wordpress.com/2016/04/06/sql-server-set-up-error-the-rpc-server-is-unavailable/

Conclusion

As simple as it is, not a lot of people know or even think to do it and my hope is that someone will find this post useful and time-saving! Also, checkout my related post on downgrading your SQL Server which uses the command-line and PowerShell similar to this.