PowerShell: Create MSSQL database

Let's create a new file for instance create-custom-database.ps1 and paste this code into:

Function Create-CustomDatabase
{
    Param (
        [Parameter(Mandatory=$True)]
        [string]$ConnectionString,
        [Parameter(Mandatory=$True)]
        [string]$NewDatabaseName
    )

    $con = New-Object Data.SqlClient.SqlConnection;
    $con.ConnectionString = $ConnectionString;
    $con.Open();

    # check database exists
    $sql = "SELECT name FROM sys.databases WHERE name = '$NewDatabaseName';";
    $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;

    $databaseExists= $False
    $rd = $cmd.ExecuteReader();
    if ($rd.Read())
    {   
        $databaseExists= $True;
    }
    $rd.Close();
    $rd.Dispose();

    #drop database
    if ($databaseExists) {

        $sql = "USE master; ALTER DATABASE [$NewDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$NewDatabaseName];"
        $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
        $cmd.ExecuteNonQuery(); 
        Write-Host "Database $NewDatabaseName was dropped!";
    }
 
    # create the database.
    $sql = "CREATE DATABASE [$NewDatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS;"
    $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
    $cmd.ExecuteNonQuery();     
    Write-Host "Database $NewDatabaseName is created!";
 
    # close & clear all objects.
    $cmd.Dispose();
    $con.Close();
    $con.Dispose();
}


# Fill this variables wit your values
$connectionString = "Data Source=.\SQLEXPRESS;initial catalog=master;Integrated Security=True;"
$newDatabaseName = "TestDatabase"

Create-CustomDatabase -ConnectionString $connectionString -NewDatabaseName $newDatabaseName

We have created Powershell function Create-CustomDatabase with two input parameters (both as mandatory) for connection string and new database name. So that's enough and we just need to call this function at the bottom of this script.

Create-CustomDatabase function does several things.

At first, we create a new database connection and open it:

    $con = New-Object Data.SqlClient.SqlConnection;
    $con.ConnectionString = $ConnectionString;
    $con.Open();

Then checks if desire database with the name we passed as a parameter exists:

    $sql = "SELECT name FROM sys.databases WHERE name = '$NewDatabaseName';";
    $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
    
    $databaseExists= $False
    $rd = $cmd.ExecuteReader();
    if ($rd.Read())
    {   
        $databaseExists= $True;
    }
    $rd.Close();
    $rd.Dispose();

If the database already exists we are gonna drop it:

    if ($databaseExists) {

        $sql = "USE master; ALTER DATABASE [$NewDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$NewDatabaseName];"
        $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
        $cmd.ExecuteNonQuery(); 
        Write-Host "Database $NewDatabaseName was dropped!";
    }

Then we are gonna create a new database:

    $sql = "CREATE DATABASE [$NewDatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS;"
    $cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
    $cmd.ExecuteNonQuery();     
    Write-Host "Database $NewDatabaseName is created!";

and at the end, we clear command and connection:

    $cmd.Dispose();
    $con.Close();
    $con.Dispose();