Rajendra Gupta
database architecture

Creating a SQL Server Database using DBATools

May 23, 2019 by

DBATools is an open source PowerShell module with useful commands to do the administrative task in SQL Server. In my earlier articles on DBATools (see TOC at the bottom) we explored installation and performing database backups, restoration, and validation with DBATools.

In this article, let’s explore creating a SQL Server database using DBATools.

New-DbaDatabase command in DBATools

Usually, we create a SQL Server database using a graphical user interface in SSMS or Transact-SQL. We can create a database using open source PowerShell module DBATools command New-DbaDatabase.

We can search for a list of available functions in DBATools with the following command.

Creating SQL Server Database using DBATools

We can create a SQL Server database using New-DbaDatabase command in DBATools.

The syntax for New-DbaDatabase command

We can get useful stuff for a command using Get-help *function* in DBATools. In the following image, you can see description and syntax for New-DbaDatabase command.

DBATools command help to get syntax

Let’s have a quick recap of SQL database architecture. We have following SQL database files in SQL Server.

  • Primary data file (*.mdf)
  • Secondary data file (*.ndf)
  • Log file (*.ldf)

Each SQL database is having a Primary file group that contains the primary data file. We can create secondary file groups and have multiple secondary data files in it.

database architecture

By default, SQL Server creates a copy of a model database if we do not specify any parameter for creating a database. Let’s create a SQL database using DBAtools.

Example 1: Create a SQL Server Database without specifying the database name

In the following DBATools command New-DbaDatabase, we specified the SQL instance name in which we want to create a new SQL database.

If we do not specify any database name, DBATools creates a new database with a random name. As we just created this database and did not have any backup, it shows last full, differential and log backup as default value.

SQL Server Database using DBATools command

As stated earlier, if we do not specify any parameter to create a SQL database, it creates a copy of a model database. Let’s compare the properties of the SQL database and the newly created database (random-808569434).

Database comparison

  1. Both Model and random-808569434 database have similar size, space available and the number of users
  2. Collation of both Model and random-808569434 database is Latin1_General_CI_AS
  3. Database owner of Model database and random-808569434 is different. We executed DBATools command under default authentication (Windows authentication), and windows user (In this case, Kashish\Test) becomes a database owner

SQL Server requires an exclusive lock on the model database to create a new SQL database. I executed this command while a model database properties window is open. SQL Server could not get exclusive lock and Crate database statement failed with the following error

Error while creating SQL Server Database

Example 2: Create SQL Server Database with specifying a database name

In the previous example, we did not specify any database name in DBATools command. Let’s specify a database name and execute the command.

SQL Server creates a new SQL database with the specified name.

Specify Database name using DBATools

We can create multiple databases as well by specifying database names separated by a comma.

Database SQLTemp1 already exists in SQL instance kashish\SQL2019CTP therefore we get a warning message for SQLTemp1 database. It creates SQLTemp2 database successfully.

Specify database name

Let’s drop databases SQLTemp1 and SQLTemp2 and rerun the DBATools command.

SQL Create Database

We can get a result of a command in a grid view to have a better visual experience. We need to specify by | Out-GridView parameter with DBATools command.

Execute the command, and it opens a new window for output as per the following image.

In the following image, we see the output in a grid format.

output in gird format

Example 3: Create a SQL Server database with Secondary files

Suppose we want to create a SQL database with the following requirements.

  • Data growth of primary data file (*.mdf) should be 40 MB
  • We should have a secondary filegroup with a secondary data file having an original size of 30 MB and auto growth 30 MB
  • Log file growth should be 30 MB

Execute following DBATools PowerShell command. In this command, we specified the following parameters.

  1. Primary data file auto growth using -PrimaryFileGrowth parameter
  2. Secondary data file auto growth using -SecondaryFileGrowth parameter
  3. Secondary Data file initial size using -SecondaryFileSize parameter
  4. Log file auto growth using -Loggrowth parameter

filegrowth while creating SQL Server Database

Let’s open database properties in SSMS and verify the conditions we specified.

Auto growth validation

You can notice that we did not specify the secondary file group name as well as the secondary data file name in the command. DBATools automatically assigns the filegroup and data file name for us. It derives the name from the database name. For example, in this example, it creates secondary filegroup SQLTemp1_MainData because database name is SQLTemp1.

verify secondary file group

By default, it creates one secondary data file if we specify any parameter related to secondary file such as SecondaryFileGrowth.

We might want to create multiple secondary data files as well. We can specify the number of secondary files using -SecondaryFileCount parameter. In the following example, we want to create three secondary data files.

specify Number of secondary data file

Now go to database properties in SSMS. We can see four secondary database files in this database. In the query, we specified only three secondary files. It creates one additional secondary data file due to specified secondary data file parameters.

Verify secondary data files

Example 4: Specify default file group as Secondary in SQL Server Database using DBATools

By default, SQL Server configures primary filegroup as default filegroup. Usually, we specify default filegroup other than primary filegroup. We can do it using DBATools parameter -DefaultFileGroup.

In the following screenshot, you can see a database created using DBATools without specifying the parameter -DefaultFileGroup.

Verify Default file group in SSMS

Let’s create another database by specifying the parameter -DefaultFileGroup to configure secondary filegroup as default.

Verify Default file group in SSMS

Example 5: Create a SQL Server database with the specified collation

By default, DBATools command creates SQL database with a default collation. If we want to specify a specific collation, we can do it using -Collation parameter.

In this example, we want to create SQL Server Database with a different collation Latin1_General_CS_AI. Execute the following command, and in the output, it shows the database collation same as we specified.

Specify a collation in SQL Create Database

Let’s verify it from database properties as well. You can see database collation in database properties under the general page.

verify database collation

Example 6: Create a SQL Server database with a specific database owner

Once we create a database, SQL Server sets database owner as a connected user security context. For example, in my case, we connected to SQL Server using windows authentication (ID – Kashish\Test). If we do any specify any database owner, SQL Server sets Windows user Kashish\Test as owner.

Suppose we want to create all SQL Server database having owner SA. In DBATools, we can specify database owner using -owner parameter.

Execute the following command to create a database with owner SA.

In the output, it still showed the database owner Kashish\Test that is my windows user. It should set an owner as SA.

SQL Create database with a specific database owner

If we verify it in the database properties, it shows the correct database owner as specified in DBATools command.

Verify database owner

Example 7: Create a SQL Server database with a maximum primary and secondary file size

We might require setting maximum size for a primary and secondary data file in SQL Server to load a limited amount of data. It might be useful to control excessive growth of a database to avoid any disk related issues.

Note: You should not set maximum file size in production until we exactly know the requirements.

We can use –PrimaryFileMaxSize and –SecondaryFileMaxSize parameters to set the maximum size of primary and secondary files respectively.

In the following example, we set the maximum size for both primary and secondary files to 100 MB.

Specify  maximum primary and secondary file size in SQL Server Database

In the following screenshot, we can see that auto growth is set to 100 MB for all data files.

Verfiy maximum file size in SQL Create Database

Example 8: Create a SQL Server database with a confirmation message

It is an excellent practice to get a confirmation prompt before we do any activity in the database. In DBATools, we can use -Confirm parameter to display a confirmation prompt. It prevents us from executing any command accidentally.

In the following example, we can see that it gives us a message – Are you sure, you want to perform this actions?

We need to provide input Y or A to execute the DBATools command.

Confirmation message

Conclusion

In this article, we explored creating SQL Server Database using PowerShell module DBATools. We will continue exploring useful command in DBATools in my future articles. If you had comments or questions, feel free to leave them in the comments below.

Table of contents

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
188 Views