Create SQL Server Database with PowerShell


By:   |   Updated: 2019-11-12   |   Comments   |   Related: More > 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:

  1. PowerShell's sqlcmd.exe equivalent, Invoke-SqlCmd cmdlet
    1. Calling a .sql script
    2. Using a PowerShell variable
  2. SQL Server Management Object (SMO) – objects to programmatically manage SQL Server
  3. 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"			
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.

# 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()
} 
			
create database with SMO

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
			
			
create database with New-DbaDatabase

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:



Last Updated: 2019-11-12


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools