hen you install many SQL Server instances, automating the installation process is an absolute must. Ideally, you want to create a master reference installation of SQL Server using the GUI installer and be able to replay the installation steps with complete fidelity in a scripted, unattended installation. Automating installations in this way is essential to developing a repeatable, predictable process.

It’s easy to do this with SQL Server 2000 because the installation process creates a setup.iss file that you can use as input to an unattended installation. Unfortunately, Microsoft removed this feature from SQL Server 2005 and there is no documented feature providing similar functionality.

However, an undocumented SQL Server 2005 feature provides similar functionality to setup.iss, provided you’re willing to use an editor to read an XML file and then edit a text file. It’s a small price to pay when the alternative is to use trial and error and hope you’ve done what you intended to do.

Using the LOGNAME Undocumented Feature

If you add the undocumented LOGNAME parameter when executing SQL Server 2005 setup.exe, it captures the GUI setup steps in an XML file named SQLSetup0001_MachineName_Datastore.xml. Adding this parameter of course necessitates starting setup.exe from a command prompt; you can’t just double-click it from Windows Explorer or allow it to start from the Autoplay of DVD installation media.

To add the LOGNAME parameter, use syntax similar to the following (Note that LOGNAME is case-sensitive and must be in all uppercase):

D:Servers>start /wait setup LOGNAME=C:setupsetup.cab

LOGNAME causes setup to create an additional log file that is briefly mentioned in SQL Server 2005 Books Online (BOL) as SQLSetup0001_MachineName_Datastore.xml. BOL mentions this is a diagnostic file that may be requested by Microsoft Product Support Services for troubleshooting installation problems, but it does not tell you how to use the LOGNAME parameter. Additionally, LOGNAME causes all of the log files, including Datastore.xml, to be written into the cab file specified in the command line. (The output cab file takes several seconds to write after the last setup dialog box disappears.) The XML log file will also be written to the standard log directory, which by default is C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFiles.

Take your Datastore.xml file from either location and open it in the editor of your choice. Find the Scope element with an ID value of 0, keeping in mind that this is undocumented and subject to change or correction. Nested within this element you will find all of the options chosen during the GUI install:

<Scope Type="MsiPropertyScope" Id="0">	<Property Id="VS">Property>	<Property Id="INSTANCENAME">MSSQLSERVERProperty>	<Property Id="INSTALLSQLDIR">C:Program FilesMicrosoft SQL ServerProperty>	<Property Id="INSTALLSQLDATADIR">C:Program FilesMicrosoft SQL ServerProperty>	<Property Id="CLSSQLDATADIR">Property>	<Property Id="IP">Property>	<Property Id="NODELIST">Property>	<Property Id="GROUP">Property>	<Property Id="SQLACCOUNT">NT AUTHORITYSYSTEMProperty>	<Property Id="SQLPASSWORD">********Property>	<Property Id="AGTACCOUNT">NT AUTHORITYSYSTEMProperty>	<Property Id="AGTPASSWORD">********Property>	<Property Id="ASACCOUNT">Property>	<Property Id="ASPASSWORD">********Property>	<Property Id="SQLBROWSERACCOUNT">NT AUTHORITYSYSTEMProperty>	<Property Id="SQLBROWSERPASSWORD">********Property>	<Property Id="SECURITYMODE">SQLProperty>

Unfortunately, you cannot take this file and use it as an input parameter to an unattended setup. However, you can copy and paste the contents of the Datasource.xml file into a custom template.ini file.

Use syntax similar to the following to perform an unattended installation:

start /wait setup LOGNAME=C:setupsetup.cab /settings C:setupmyTemplate.ini /qn

The /qn flag suppresses all dialog boxes. (Refer to Books Online if you need additional information.)

After the installation is finished, you can check its error status by entering the following on the command line:

echo %errorlevel%

A return status of 3010 means a reboot is required. See this support.microsoft.com entry for a complete list of return status values.

Using the template.ini File for an Unattended Installation

Microsoft provides a sample parameter file named template.ini for controlling unattended installations of SQL Server 2005. You can find this file in the installation media, in the same folder as setup.exe. I have removed the comments from the following template.ini file excerpt so you can see just the parameters:

[Options]INSTALLSQLDIR=INSTALLSQLDATADIR=INSTANCENAME= SQLBROWSERACCOUNT= SQLBROWSERPASSWORD=SQLACCOUNT=SQLPASSWORD=AGTACCOUNT=AGTPASSWORD=ASACCOUNT=ASPASSWORD=SECURITYMODE=

Without presenting the entire contents of the Datasource.xml file or the template.ini file, these excerpts provide enough information for you to see the one-to-one correspondence between attribute names in Datasource.xml and parameter names in template.ini. If you copy the values from Datasource.xml into template.ini, you can replicate the functionality of SQL Server 2000’s setup.iss.

When you run an unattended installation using your edited template.ini file, you may want to use the LOGNAME parameter to generate a Datasource.xml file from the unattended installation. You can compare the two Datasource.xml files. They should match with great fidelity, but some minor differences will exist. For example, a Datasource.xml file contains values indicating whether it was created by an attended installation or an unattended installation. Obviously, those values will not match, but you should be able to reason through the differences in the files.

Unattended Installations of SQL Server 2005 Express

An unattended installation of any version of SQL Server 2005 requires that you execute the setup.exe from the command line. SQL Server 2005 Express does not make the setup.exe accessible by default. Instead, it is hidden inside the installer executable for the product. A default installation of Express extracts all of the media files to a temporary directory that is deleted upon completion of the Express installation. To gain command line access to the Express setup.exe, you must manually extract all of the installation files to disk. Run the SQL Server 2005 Express installer executable from a command line using syntax similar to the following:

C:2005Express>sqlexpr_adv.exe /x

The /x switch tells the installer executable to extract all of the media files instead of performing the installation. A dialog box appears and you specify the location for the extracted files. After all of the SQL Server 2005 installation files are extracted, run the setup.exe from a command line using the LOGNAME parameter.

The Undocumented Solution to Unattended Installs

By using the undocumented LOGNAME parameter on a setup command line, you can capture all of the steps of an automated installation. You can achieve the same results as a setup.iss file by using a text editor as the bridge between the reference attended installation and the unattended installation. Using undocumented features isn’t for everyone, but many people prefer the mysteries of the undocumented unknown to the certain frustrations of having no feature at all.