Automating SQL Server Builds

Problem

I am frequently building up my SQL Server environments, especially my test lab. However, manually performing SQL Server backups, running scripts to set up security, and the rest of the tasks are time-consuming. What can I do to automate things?

Solution

We will start with the assumption that we have a working virtual machine (VM) to install SQL Server and perform other setup tasks. The process we’ll follow is basically three steps:

First, we’ll install SQL Server. Then we’ll run any necessary administrative scripts. Finally, we’ll perform any database restores or builds required. We’ll break down the processes a bit further, but at a high level, these are the tasks we’ll need to perform.

We will walk through the steps and the very end there is an entire PowerShell script to do this.

Step 1: Install SQL Server

We’re going to leverage PowerShell to do the heavy lifting for our automation. Therefore, the first thing to do is to create a PowerShell script, say InstallSQL.ps1, which will execute the steps we need in turn.

Starting with step one, Install SQL Server, we are going to take advantage of SQL Server’s command-line installation options. However, before we start the installation process, we should create any directories that SQL Server will need as part of the installation. For instance, for my personal SQL Server lab environment, I standardize the directories for my SQL Server database files and my database backups.

First Part of PowerShell Script

Therefore, the very first lines of my PowerShell script create the directories I’ll need. If you’re automating SQL Server installations for enterprise environments, you’ll possibly have the folders on different drives, and you may have more directories than I have here. But here’s what I create in my lab environment:

c:
cd \ 
md SQLData 
md SQLBackup 

I’m creating C:\SQLData for my database files and C:\SQLBackup for my backups. I’ll specify these directories in the command line options later on.

SQL Server Setup

Speaking of command line options, we’re going to execute SQL Server’s setup, but likely you have an .ISO file that you’ll need to mount. Here’s where we’ll leverage PowerShell’s capabilities. We can mount the .ISO, determine the drive letter that it is mounted to, build the path to setup.exe, and then run setup using our command line options.

So, let’s mount the ISO and get the drive letter. In this case, I’m mounting the SQL Server 2022 Developer Edition ISO, which I’ve located in C:\install. Note the backtick after -ImagePath at the end of the line. This tells PowerShell that the rest of the command continues on the next line.

$mountResult = Mount-DiskImage -ImagePath ` 
'c:\install\enu_sql_server_2022_developer_edition_x64_dvd_7cacf733.iso';
$setupPath = ($mountResult | Get-Volume).DriveLetter + ":\setup.exe"

The drive mounting could always fail, or the ISO might be missing. We’ll add a simple if-else to test for this, and if something is wrong, it’ll exit the PowerShell script. If all is well, we’ll kick off the SQL Server install process using the Start-Process PowerShell cmdlet. This will allow us to execute the setup file according to the path we’ve built and specify the command-line options we want to use:

if (Test-Path $setupPath) {
    Write-Host "Setup file found at $setupPath"
    Start-Process -FilePath $setupPath -ArgumentList "/Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install ` 
/FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=`"MyDomain\User`" `
/USESQLRECOMMENDEDMEMORYLIMITS /UPDATEENABLED=TRUE /UPDATESOURCE=MU `
/SQLSVCSTARTUPTYPE=`"Automatic`" /AGTSVCSTARTUPTYPE=`"Automatic`" /TCPENABLED=1 `
/SQLBACKUPDIR=`"C:\SQLBackup`" /SQLUSERDBDIR=`"C:\SQLData`" /SQLUSERDBLOGDIR=`"C:\SQLData`"" -Wait
} else {
    Write-Host "Setup file not found at $setupPath"
    exit 1
}

SQL Server Setup Options

I’ve specified quite a few command-line options, so let’s go through them. In the code, you’ll see the quotation marks escaped with a backtick. This is required because we are using quotation marks to encapsulate all the command-line options we are declaring for setup.exe.

Step 2: Run Admin Scripts

With SQL Server installed, our next step is to run any admin scripts we might want for setup. However, we’re likely going to need SQLCMD to do so, and this should have just gotten installed with our SQL Server install. That means in the process window executing our PowerShell script, the path is set to what it was before SQL Server was installed, meaning the path hasn’t been updated to include the new directories for SQL Server.

Setup Environment Variable

So, if we just tried to execute SQLCMD, we’d get an error indicating that the file wasn’t found. Therefore, before we execute any scripts, we must refresh the path in the process window. We can do so by setting the variable $env:Path to the refreshed machine and user paths. Note that this is all one line, even though it’ll likely wrap here on-screen.

$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User")

Using Start-Process to Call Scripts

We are again using Start-Process and calling additional scripts that comprise the commands we want to run. Here I’m calling a CMD script that specifies to run SQLCMD and executing the appropriate script. Within our central PowerShell script, I’ll want to add additional sysadmins. Therefore, I have a .CMD script for that. However, I’ve chosen to put all my files centrally in C:\install. If you look closely at what we have thus far, the script navigated to C:\ to create the SQLBackup and SQLData directories. So, we’ll need to navigate back to C:\install, and then we can begin executing our additional scripts:

cd \install
Start-Process -FilePath .\add_sysadmins.cmd

We will create a file called add_sysadmins.cmd script that contains:

SQLCMD -E -C -i .\add_sysadmins.sql 

If you’re wondering about the flags, -E indicates to make a trusted connection, and -C says to accept the SQL Server certificate, even if it’s the self-signed one generated when the service is installed. In our case, since this is a test lab, I don’t have any other cert installed on the server, so I need the -C flag. In a production environment, it’s best to have a certificate for the SQL Server from a trusted certificate authority with SQL Server properly configured to use it. Finally, the -i tells SQLCMD to use the SQL script provided.

In the add_sysadmins.sql file the script is basic – I’m adding the DBA security group to the sysadmin role:

IF NOT EXISTS(SELECT name FROM sys.server_principals WHERE name = 'MyDomain\MyDBAGroup')
    CREATE LOGIN [MyDomain\MyDBAGroup] FROM WINDOWS;
GO
ALTER SERVER ROLE sysadmin
    ADD MEMBER [MyDomain\MyDBAGroup];
GO

So we have created two files add_sysadmins.cmd and add_sysadmins.sql.

If you have other admin scripts you want executed, you can structure them the same way and use Start-Process to execute them in turn. In fact, that’s how we’ll do the database restores.

Step 3: Restore Databases

Now it’s time to build/restore databases. I’m going to use the Microsoft sample databases. Back to the main PowerShell script, here are the last few lines:

Start-Process -FilePath .\install_pubs_nw_dbs.cmd
Start-Process -FilePath .\restore_adventureworks.cmd
Start-Process -FilePath .\restore_worldwideimporters.cmd

Northwind and Pubs databases

For the Northwind and Pubs databases, there are SQL scripts that Microsoft provides to build the databases. They aren’t restored from backup. There’s also a gotcha with Northwind to watch out for, which I cover in my article on the SQL Server sample databases. Check out this article Microsoft SQL Server Sample Databases to learn more.

For this you would create the install_pubs_nw_dbs.cmd that looks like this:

SQLCMD -E -C -i .\install_pubs_nw_dbs.sql

Also, create the install_pubs_nw_dbs.sql file and include alld of the SQL code to build these databases.

AdventureWorks and WideWorldImporters databases

For the AdventureWorks and WideWorldImporters databases, we’ll need to specify file paths. I want to use the ones I’ve previously defined, meaning the SQL scripts look like this for AdventureWorks (note that the lines wrap).

For this you would create the restore_adventureworks.cmd that looks like this:

SQLCMD -E -C -i .\restore_adventureworks.sql

For the restore_adventureworks.sql the contents would look like this.

USE master;
GO
RESTORE DATABASE [AdventureWorks2022] FROM  DISK = N'C:\install\AdventureWorks2022.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2022' TO N'C:\SQLData\AdventureWorks2022.mdf',  MOVE N'AdventureWorks2022_log' TO N'C:\SQLData\AdventureWorks2022_log.ldf',  NOUNLOAD,  STATS = 5
GO 
RESTORE DATABASE [AdventureWorksDW2022] FROM  DISK = N'C:\install\AdventureWorksDW2022.bak' WITH  FILE = 1,  MOVE N'AdventureWorksDW2022' TO N'C:\SQLData\AdventureWorksDW2022.mdf',  MOVE N'AdventureWorksDW2022_log' TO N'C:\SQLData\AdventureWorksDW2022_log.ldf',  NOUNLOAD,  STATS = 5
GO 
RESTORE DATABASE [AdventureWorksLT2022] FROM  DISK = N'C:\install\AdventureWorksLT2022.bak' WITH  FILE = 1,  MOVE N'AdventureWorksLT2022_Data' TO N'C:\SQLData\AdventureWorksLT2022.mdf',  MOVE N'AdventureWorksLT2022_Log' TO N'C:\SQLData\AdventureWorksLT2022_log.ldf',  NOUNLOAD,  STATS = 5
GO 

WideWorldImporters

For this you would create the restore_worldwideimporters.cmd that looks like this:

SQLCMD -E -C -i .\restore_worldwideimporters.sql

For the WideWorldImporters the contents of restore_worldwideimporters.sql look like this.

USE master;
GO
RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'C:\install\WideWorldImporters-Full.bak' WITH  FILE = 1,  MOVE N'WWI_Primary' TO N'C:\SQLData\WideWorldImporters.mdf',  MOVE N'WWI_UserData' TO N'C:\SQLData\WideWorldImporters_UserData.ndf',  MOVE N'WWI_Log' TO N'C:\SQLData\WideWorldImporters.ldf',  MOVE N'WWI_InMemory_Data_1' TO N'C:\SQLData\WideWorldImporters_InMemory_Data_1',  NOUNLOAD,  STATS = 5
GO
RESTORE DATABASE [WideWorldImportersDW] FROM  DISK = N'C:\install\WideWorldImportersDW-Full.bak' WITH  FILE = 1,  MOVE N'WWI_Primary' TO N'C:\SQLData\WideWorldImportersDW.mdf',  MOVE N'WWI_UserData' TO N'C:\SQLData\WideWorldImportersDW_UserData.ndf',  MOVE N'WWI_Log' TO N'C:\SQLData\WideWorldImportersDW.ldf',  MOVE N'WWIDW_InMemory_Data_1' TO N'C:\SQLData\WideWorldImportersDW_InMemory_Data_1',  NOUNLOAD,  STATS = 5
GO

As you can see, the CMD files are identical to the one for adding sysadmins, except the script specified is different. If you have additional DBs to recover, follow a similar methodology.

The Final PowerShell Script

Let’s put together the single PowerShell script, installSQL.ps1, so we can see the whole thing at one glance:

c:
cd \ 
md SQLData 
md SQLBackup 
$mountResult = Mount-DiskImage -ImagePath ` 
'c:\install\enu_sql_server_2022_developer_edition_x64_dvd_7cacf733.iso';
$setupPath = ($mountResult | Get-Volume).DriveLetter + ":\setup.exe"
if (Test-Path $setupPath) {
    Write-Host "Setup file found at $setupPath"
    Start-Process -FilePath $setupPath -ArgumentList "/Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install ` 
/FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=`"MyDomain\User`" `
/USESQLRECOMMENDEDMEMORYLIMITS /UPDATEENABLED=TRUE /UPDATESOURCE=MU `
/SQLSVCSTARTUPTYPE=`"Automatic`" /AGTSVCSTARTUPTYPE=`"Automatic`" /TCPENABLED=1 `
/SQLBACKUPDIR=`"C:\SQLBackup`" /SQLUSERDBDIR=`"C:\SQLData`" /SQLUSERDBLOGDIR=`"C:\SQLData`"" -Wait
} else {
    Write-Host "Setup file not found at $setupPath"
    exit 1
}
$env:Path = [System.Environment]::GetEnvironmentVariable("Path","Machine") + ";" + [System.Environment]::GetEnvironmentVariable("Path","User")
cd \install
Start-Process -FilePath .\add_sysadmins.cmd
Start-Process -FilePath .\install_pubs_nw_dbs.cmd
Start-Process -FilePath .\restore_adventureworks.cmd
Start-Process -FilePath .\restore_worldwideimporters.cmd

Make sure all the files are together and the paths all match up. For instance, you might have all the files in a different location than C:\install. Once you’ve tested your scripts, you could even automate the file copy and the execution of installSQL.ps1.

Next Steps

• Learn about the sample databases provided for SQL Server over the years and download your own copies.
• Read the Microsoft documentation on command-line installation options.
• Find other ways you can use PowerShell to work with SQL Server.
• Understand how to perform database refreshes without rebuilding SQL Server.

K. Brian Kelley

Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.

• MSSQLTips Awards: Author of the Year Contender – 2015, 2017 | Champion (100+ tips) – 2014