When I presented by 1 Hour Server session at SQL Saturday 142 in Waltham last weekend, one of the things I stressed to my audience is that this is insanely easy and that anybody can do this.

There are really two steps to my process. First, I automate the setup process. The next step is to take my configuration checklist and automate it.

When I run through the installer, I emphasize that it’s a lot of non-value added time. You basically spend a lot of time helping the installer build a response file. Once you have that file, you can recycle it over and over, saving you hours of time.

The key is walking through the installer and grabbing that file at the end. After that, I do a quick edit of that file by commenting out the UIMODE line. I then throw that into my batch file to have it run the setup silently.

[shell]“C:\Software\SQL2012\setup.exe” /QUIETSIMPLE /IACCEPTSQLSERVERLICENSETERMS /configurationfile=“C:\1HourServer\ConfigurationFile2.txt” /SAPWD=“SAPasswordgoeshere” /SQLSVCPASSWORD=“serviceaccountpasswordgoeshere” /AGTSVCPASSWORD=“agentaccountpasswordgoes_here” [/shell]

After that, I walk through my scripts and put them into the same batch file. I have everything that I do in reusable scripts, such as creating my backup SQL Agent jobs to calling Michelle Ufford’s index maintenance scripts.

One of the little nuggets here is that when you do something with SQL Management Studio, click the little “Script” button and have it generate the SQL for you. Once you have that, you can recycle that over and over.

The other thing I do is right-click on a SQL Agent job and generate the SQL to recreate the job. That too becomes a script.

Then I take those scripts and call them with SQLCMD in my batch file.

Ultimately, I have a batch file that looks like this. The last line just reboots the server for good measure.

[TEXT]”C:\Software\SQL2012\setup.exe” /QUIETSIMPLE /IACCEPTSQLSERVERLICENSETERMS /configurationfile=”C:\1HourServer\ConfigurationFile2.txt” /SAPWD=”SAPasswordgoeshere” /SQLSVCPASSWORD=”serviceaccountpasswordgoeshere” /AGTSVCPASSWORD=”agentaccountpasswordgoes_here”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\mailprofile.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\cdbaoperator.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\enabledbmailalerts.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\system_alerts.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\audit.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\backups.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\tlogbackups.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\revlogin.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\purge_backup_and_agent.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\failed_agents.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\checkdb_full.sql”
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd” -S localhost -E -i “c:\1HourServer\checkdb_physical.sql”
shutdown /r /t 0 /d p:4:2[/TEXT]

Really, it’s that easy.

Here are a few resources I mentioned in the session.

Script library at sqlservercentral.com

Ola Halengren’s Maintenance Scripts

Michelle Ufford’s Index Maintenance Scripts

Paul Randal’s Blog (or any other author at sqlskills.com)