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. Choose a location to extract the installation files to.
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:Scriptssql-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.
start /wait setup.exe /qb /settings C:Scriptssql-2005-express.ini |
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
28 Responses to “How to perform an unattended installation of SQL Server 2005 Express”
Leave a Reply
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 ?
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.
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 ?
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.
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
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.
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!
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
Hi Sampath,
Yes, you can do this using the INSTALLSQLDATADIR= directive in your unattended/answer file.
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
Hi Jason and Chris
I was having problem to get SQL instance installed.
This really save my day:
SQLACCOUNT=”NT AUTHORITY\SYSTEM”
Ping
Hi
I want install SQL Server 2005 Express Edition as a prerequisites with my application , created using VS 2005, How can I use template.ini to do this ?
@ashish: There are plenty of Microsoft articles that cover the specifics of including SQL Express 2005 as a prerequisite for your applications. Here are some links that will help you:
Embedding SQL Server Express into Custom Applications
Walkthrough: Using a Custom Action to Create a Database at Installation
Forum: How to install SQL Server Express as a prerequisite?
Hope that helps!
RE: INSTANCENAME=MSSQLSERVER
Seems I remember the default name for SQL Server 2005 Express is SQLExpress.
I seen many starter kits with connection strings like:
could you comment on that?
BTW I have SQL Server 2008 installed and wish to also have SQL Server 2005 Express installed. Do you think that will work OK?
I have SQL Server 2008 and Visual Studio 2008 installed in J disk but Visual Studio (which has SQL Server 2005 Express as a component) will only enable me to install SQL Studio 2005 on C, the system disk.
So I plan to try your approach to install it on j. Think it should work?
Thanks a lot
I don’t know why the connection strings didn’t show in my post.
Here is one again:
Data Source=.\SQLExpress;Integrated Security=True;User
Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf”
providerName=”System.Data.SqlClient
Hi,
You really saved my day. The [SQLACCOUNT=”NT AUTHORITY\SYSTEM”] really helped. I also enabled this option, [ADDUSERASADMIN=1]. I think it might be handy on Vista machines..
Thanks a lot…
@Mark: Thanks for your comment. In case you weren’t sure, the ADDUSERASADMIN option is only for the SQL Server Administrator role, not the Windows Administrator role/group. If modification of files is prohibited unless you’re a *local* administrator running in the administrative context (Run As Administrator) the ADDUSERASADMIN option won’t help for anything other than SQL Server changes that require administrative privileges. Please see http://msdn.microsoft.com/en-us/library/ms143401(SQL.90).aspx for Microsoft’s documentation on this option.
Hi it is a nice article.I want to know how to deploy unattended installation using .net setup and deployment project can u send me a sample solution for this it will be help full (i have very little knowledge in .net setup and deployment ).
Thanks in advance
sathish: Sorry but no, I can’t make you a sample solution. What you’re asking for isn’t really a ’sample’ … it’s something you’d need to commission someone to do (which wouldn’t be free).
hi created a sql installer class using sql embedded installer sample how can i had this sql installer class as my prerequesties during setup and deplopyment project
Chris,
I’m getting an error message: “Settings file C:\sql-2005-express.ini is missing or invalid. Please ensure this file exists and is accessible.” I’ve used your script file with minimal changes. Any idea what is happening to cause this error message? Thanks!
Gordon: Hmmm I haven’t seen that particular error before. I see you’re using a file in the root of C: drive though – depending on your OS installation programs *might* be restricted from reading from there (e.g. Windows 7 is really picky about what actions you can take with the root of the C: drive). Have you tried running the same steps but with the .ini file in a directory that’s nothing to do with Windows e.g. C:\Data perhaps?
Chris,
I am trying to get JMPS_SQL_SERVER to install using the .ini file. Is there a setting within the .ini file that needs to be changed for this to install?
Thanks
Pete: Please excuse my ignorance but what is JMPS_SQL_SERVER? I searched for it and found something to do with FalconView and spatial/map data – is what it relates to?
Yes, I am trying to create a package to push Falconview to multiple workstations. SQL server is a prerequisite for Falconview. JMPS_SQL_SERVER is required for the program to open. I used the setup.exe with the .ini file but the instance JMPS_SQL_SERVER portion of SQL Server Express Edition does no load. Any suggestion would be very helpful.
Hi Criss,
Realy helpfull article, I thank you very much.
Warm Regards
Vinu M
Hi Chris,
I am trying to install Sql Server 2005 Express with the following arguments
/qn ADDLOCAL=SQL_Engine,SQL_Data_Files INSTANCENAME=SqlExpress SQLAUTOSTART=1 AGTAUTOSTART=1 SECURITYMODE=SQL SAPWD=Password123 DISABLENETWORKPROTOCOLS=2 SQLACCOUNT=’NT AUTHORITY\LOCAL SYSTEM’ AGTACCOUNT=’NT AUTHORITY\LOCAL SYSTEM’
However I keep getting the following error:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account
I tried using NT Authority\System. Same error. You have any ideas? BTW I am using Windows Server 2008
MT: I’ve received that error numerous times in the past and it’s always been an error in the username or password … sorry. I think UAC is enabled by default in Windows Server 2008 so you could try that. Unfortunately I don’t have a Windows Server 2008 system here to test it on.