Create SQL Server Database with PowerShell

By: Joe Gavin | Comments | Related: > PowerShell

Problem

Can I create a SQL Server database using a PowerShell script?

Solution

Of course, you can. And as with most things, there is more than one way to solve the problem, so we'll look at these three different methods to solve the same problem:

PowerShell's sqlcmd.exe equivalent, Invoke-SqlCmd cmdlet
1. Calling a .sql script
2. Using a PowerShell variable
SQL Server Management Object (SMO) – objects to programmatically manage SQL Server
dbatools – extremely powerful PowerShell tools to manage SQL Server

For our examples we're going to create the following database:

SQL Server Named Instance is JGAVIN-L\SQL2017
Database is called MyDatabase
Data and log files are in C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA (defaults)
Database Recovery Model is Simple – we won't be taking transaction log backups as this is just a play database
Initial data file size is 1024 MB and file will automatically grow by 256 MB
Initial log file size is 512 MB and file will automatically grow by 128 MB
Database Owner is sa

Versions used here are:

SQL Server 2017 Cumulative Update (CU) 16 Developer Edition installed on my laptop
PowerShell 5.1.17134.858

Method # 1a – Create SQL Server Database Using PowerShell Invoke-SqlCmd calling a .sql script

First, check to see that you have the SqlServer module installed. You'll have it if you've installed SQL Server Management Studio (SSMS) but let's be sure.

Get-InstalledModule -Name "SqlServer"

check to see if SqlServer module is installed

If it doesn't show up, install it by running Install-Module -Name "SqlServer" running PowerShell as an administrator on the machine you're installing it on.

Let's look at two ways to use the Invoke-SqlCmd cmdlet which is very similar to the sqlcmd.exe utility.

The first way will call a SQL script using the -InputFile switch which is the equivalent of sqlcmd's -i switch.

Paste the SQL code below into a file in C:\scripts called Create_MyDatabase.sql.

CREATE DATABASE [MyDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 262144KB )
 LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf' , SIZE = 524288KB , FILEGROWTH = 131072KB )
GO
 
USE [master]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
GO
 
ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO

Then call the file with the Input-File switch.

Invoke-SqlCmd -ServerInstance JGAVIN-L\SQL2017 -InputFile C:\scripts\Create_MyDatabase.sql

run Create_MyDatabase.sql with Invoke-SqlCmd

Method # 1b – Create SQL Server Database Using PowerShell Invoke-SqlCmd and a PowerShell variable

Another way to do this with Invoke-SqlCmd is to is to paste the same SQL we used to create create_MyDatabase.sql into a PowerShell variable like this and call it with the -Query switch.

#import SqlServer module
Import-Module -Name "SqlServer"
 
# create variable with SQL to execute
$sql = "
CREATE DATABASE [MyDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 262144KB )
 LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyDatabase_log.ldf' , SIZE = 524288KB , FILEGROWTH = 131072KB )
GO

USE [master]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
GO

ALTER AUTHORIZATION ON DATABASE::[MyDatabase] TO [sa]
GO "
Invoke-SqlCmd -ServerInstance JGAVIN-L\SQL2017 -Query $sql

create database with SQL in a PowerShell variable using Invoke-SqlCmd

Method # 2 - Create SQL Server Database Using PowerShell and SQL Server Management Object (SMO)

Check to see that you have the SqlServer module installed.

Get-InstalledModule -Name "SqlServer"

If it doesn't show up, install it by running Install-Module -Name "SqlServer" running PowerShell as an administrator on the machine you're installing it on.

# import SqlServer module
Import-Module -Name "SqlServer"
 
# set instance and database name variables
$inst = "JGAVIN-L\SQL2017"
$dbname = "MyDatabase"  
 
# change to SQL Server instance directory  
Set-Location SQLSERVER:\SQL\$inst        
 
# create object and database  
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -Argumentlist $inst, $dbname  
$db.Create()  
 
# set recovery model
$db.RecoveryModel = "simple"
$db.Alter()
 
# change owner
$db.SetOwner('sa')
 
# change data file size and autogrowth amount
foreach($datafile in $db.filegroups.files) 
{
 $datafile.size= 1048576
 $datafile.growth = 262144
 $datafile.growthtype = "kb"
 $datafile.alter()
}
 
# change log file size and autogrowth
foreach($logfile in $db.logfiles)
{
 $logfile.size= 524288
 $logfile.growth = 131072
 $logfile.growthtype = "kb"
 $logfile.alter()
}

Method # 3 - Create SQL Server Database Using PowerShell and dbatools

We've seen two other methods to create a database with PowerShell, but I've saved the best for last. This method uses dbatools.

Before we continue, Let's take a minute to discuss what dbatools is all about. According to https://dbatools.io/commands/, "dbatools is a free PowerShell module with over 500 SQL Server best practice, administration, development and migration commands included."

We're only going to look at two of them. The first cmdlet is called New-DbaDatabase. As the name implies it's used to create a new database. You can find more detailed information on it here.

First, check to see that you have the dbatools module installed.

# verify you have dbatools module installed
Get-InstalledModule -Name "dbatools"

check to see if dbatools module is installed

If it doesn't show up, install it by running Install-Module -Name "dbatools" running PowerShell as an administrator on the machine you're installing it on.

$SqlInstance = 'JGAVIN-L\SQL2017'                                                   # SQL Server name 
$Name = 'MyDatabase'                                                                # database name
$DataFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\' # data file path
$LogFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\'  # log file path
$Recoverymodel = 'Simple'                                                           # recovery model
$Owner = 'sa'                                                                       # database owner
$PrimaryFilesize = 1024                                                             # data file initial size
$PrimaryFileGrowth = 256                                                            # data file autrogrowth amount
$LogSize = 512                                                                      # data file initial size
$LogGrowth = 128                                                                    # data file autrogrowth amount
 
New-DbaDatabase -SqlInstance $SqlInstance -Name $Name -DataFilePath $DataFilePath -LogFilePath $LogFilePath -Recoverymodel $Recoverymodel -Owner $Owner -PrimaryFilesize $PrimaryFilesize -PrimaryFileGrowth $PrimaryFileGrowth -LogSize $LogSize -LogGrowth $LogGrowth | Out-Null

We can use the Get-DbaDatabase dbatools cmdlet to display information on our new database. You can find more detailed information on it here. Note, I directed the output of NewDbaDatabase out to NULL just to clean up the output just because it didn't correctly report the new database owner. Get-DbaDatabase will show us the correct results.

Get-DbaDatabase -SqlInstance $SqlInstance -Database $Name

display information about our new database with Get-DbaDatabase

Next Steps

You can find more information PowerShell, Invoke-SqlCmd and dbatools here:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips