11Jun How to perform an unattended installation of SQL Server 2005 Express
To continue on with the theme of unattended installation-related posts lately, what if you want to install SQL Server 2005 Express Edition in unattended mode? SQL Server 2000 had the ability to save the installation into a file called setup.iss - how nice of Microsoft to remove this feature from SQL Server 2005 … however, let’s get rolling.
Creating an unattended installation of SQL Server 2005 Express can seem like a pretty involved task at first. It can be involved too, if you want. For this example I’m going to perform a basic installation with minimal customisation so you can see the process at work.
Where to start? As with most unattended installations these days you need to create an answer file that is passed as a parameter to setup.exe … hmm, what setup.exe? When you download SQL Server 2005 Express it’s a file called SQLEXPR.EXE - no setup.exe in sight. There is a way to get it though. Let’s sidetrack for a minute and see how.
2. When the first screen appears DON’T DO ANYTHING!
3. Look in the root of C: drive (C:\) for a recently created folder that you didn’t make. Unless you have a virus doing weird stuff this will probably be the temporary folder the SQL Express installation has been extracted into. It will be a randomly-named folder, e.g. the one I just did was called "2d4f168225a7918ed69f49629587"
4. Copy the contents of this folder to another folder on your system. Note that there will be a file called "$shtdwn$.req" in there that you can’t copy - that’s fine, you don’t need it.
5. Once you’ve got a copy of all the files in the temporary folder (excluding $shtdwn$.req!) you can safely quit the installation program. No changes have been made at this point.
Now that you’ve got a nicely extracted copy of the installation files you can carry on. The above steps were necessary for what’s coming up.
Look in the folder you copied the installation files to and locate a file called "template.ini". Copy it somewhere and open it in your favourite text editor. Personally I use Microsoft Visual Studio Express Edition - it’s free and works perfectly well as a text editor as well a cut-down version of Microsoft’s flagship development environment, Visual Studio. Nice for cranking out a bit of code too … if you don’t plan to sell it.
The template.ini is actually VERY well commented - nice one Microsoft. This file is in the same format as the template.ini that comes with the full versions of SQL Server and contains a whole load of options that you don’t want to and simply can’t use with SQL Server Express. If you want to, have a read through the file and see what’s possible - here’s where you can make the installation pretty involved if you want to.
For our purposes though the installation is going to be pretty basic. The contents of the complete file are available for download at the bottom of this article (do that later). Make all the changes you think are necessary and save the file as C:\Scripts\sql-2005-express.ini.
Once you have your answer file all nicely setup and ready to use you’ll need to know how to use it. A bit of a warning here first. Without specifying all the upgrade options in the answer file I had problems running the unattended installation on a system that already had the SQL Server Native Client - it kept spewing errors about retrying the installation using a valid copy of sqlncli.msi. Either specify the upgrade options or run the installation on a system that’s never had SQL Server installed or has already had EVERYTHING SQL-related removed.
From a command prompt (or Start > Run if you’re into that) run the following command. Remember to change the file and path names to the ones that match your system.
If you’ve done everything right this will start the installation process and you’ll end up with a SQL Server 2005 default instance called MSSQLSERVER running on your system. Easy!
Click here to download Complete sql-2005-express.ini file used in this article
August 19th, 2008 at 00:26
Maybe you can help, cause i’ve pretty much give up :-/
These are the settings i need:
Uncheck for “Hide the advanced configuration options”
Enable all features except “Replication”
i need it as “Default Instance”
Using “Local System and “Use the build-in system account”
I need both SQL Server and Browse
and i need Mixed Mode
Is it possible to do that in the script file ?
August 19th, 2008 at 11:26
Hi Sefy,
Yes, all the options you’ve asked about can be set in the script file. Here you go …
There is a section that begins with ADDLOCAL. Replication isn’t a valid option for SQL Express so I assume you’re trying to install SQL Express w/ Advanced Services? If so, set the ADDLOCAL= section to something like the following:
ADDLOCAL=SQL_Engine,SQL_Data_Files,Client_Components,Connectivity,SQL_SSMSEE,SDK
That will install the SQL Engine, data files, client components, client connectivity, management studio express and the SQL SDK. Note that SQL_SSMSEE is only valid for SQL Express w/ Advanced Services. Simply add ‘Replication’ to the list above if you *do* want to install replication (remember to comma-separate the options …)
For the default instance just set the INSTANCENAME= to the following:
INSTANCENAME=MSSQLSERVER
For the browser service, set the SQLBROWSERAUTOSTART= section to the following:
SQLBROWSERAUTOSTART=1
If you want SQL and the SQL Agent to auto start too, set the above section to the following:
SQLBROWSERAUTOSTART=1
SQLAUTOSTART=1
AGTAUTOSTART=1
For mixed mode, set the SECURITYMODE= section to the following:
SECURITYMODE=SQL
Note that if you configure your installation for mixed mode you also need to specify a strong SA password, for example:
SECURITYMODE=SQL
SAPWD=sQ15Erv3R
To specify that the SQL Server service should use Local System as the credential, set the SQLACCOUNT= section to the following:
SQLACCOUNT=”NT AUTHORITY\LOCAL SERVICE”
Hope that helps.
August 19th, 2008 at 16:20
First let me say thank you!
Now, Is all this possible as a command line switch on the SQLEXPR32.EXE or only on the SETUP.EXE that was extracted using the script file ?
August 19th, 2008 at 16:43
Hi Sefy,
Yes I believe most of the options above can be specified on the command line without using an unattended script information file.
For example you can run the following command:
SETUP.EXE SAPWD=sQ15Erv3R
That will install SQL Express with the strong password supplied. Although I haven’t done it and I haven’t looked into it myself I don’t see any reason why you can’t pass the other options as command-line parameters. For example:
SETUP.EXE ADDLOCAL=SQL_Engine,SQL_Data_Files,Client_Components,Connectivity,SQL_SSMSEE,SDK INSTANCENAME=MSSQLSERVER SQLBROWSERAUTOSTART=1 SECURITYMODE=SQL SQLACCOUNT=”NT AUTHORITY\LOCAL SERVICE”
Does that help? Let me know if it works by posting another comment here.
August 19th, 2008 at 16:48
Thanks Chris, i’ll give it a test later on at work and if it does work (or not) then i’ll give you a buzz
September 23rd, 2008 at 22:12
Thanks. This really saved me a lot of time. The only hitch was that in my case (XP Pro), the service would refuse to start during the install thereby aborting it. Adding this option: SQLACCOUNT=”NT AUTHORITY\SYSTEM” fixed that.
September 23rd, 2008 at 22:34
Hi Jason,
I’m glad my article helped you. Thanks for the tip re NT AUTHORITY\SYSTEM - it will come in very handy for someone!
September 30th, 2008 at 23:43
Hi,
For the SQL Express unattended installation, can we specify the Data path?
I require the data path to be just below the application path.
e.g. \Program Files\MyApplication\Data
thanks,
Sampath
October 1st, 2008 at 00:20
Hi Sampath,
Yes, you can do this using the INSTALLSQLDATADIR= directive in your unattended/answer file.
October 31st, 2008 at 09:57
Hi
If I would like to install SQL Server 2005 Express as a prerequisites with my application which is created using VS 2008, How can I set the above parameters?
thanks
Khayralla