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.
|
1 |
> Get-Help *Database* |
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.
|
1 |
>Get-help New-DbaDatabase |
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.
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.
|
1 |
> New-DbaDatabase -SqlInstance kashish\SQL2019CTP |
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.
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).
- Both Model and random-808569434 database have similar size, space available and the number of users
- Collation of both Model and random-808569434 database is Latin1_General_CI_AS
- 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
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.
|
1 |
> New-DbaDatabase -Name SQLTemp1 -SqlInstance .\SQL2019CTP |
SQL Server creates a new SQL database with the specified name.
We can create multiple databases as well by specifying database names separated by a comma.
|
1 |
> New-DbaDatabase -Name SQLTemp1, SQLTemp2 -SqlInstance kashish\SQL2019CTP |
Database SQLTemp1 already exists in SQL instance kashish\SQL2019CTP therefore we get a warning message for SQLTemp1 database. It creates SQLTemp2 database successfully.
Let’s drop databases SQLTemp1 and SQLTemp2 and rerun the DBATools command.
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.
|
1 |
> New-DbaDatabase -Name SQLTemp1,SQLTemp2 -SqlInstance kashish\SQL2019CTP | Out-GridView |
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.
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.
- Primary data file auto growth using -PrimaryFileGrowth parameter
- Secondary data file auto growth using -SecondaryFileGrowth parameter
- Secondary Data file initial size using -SecondaryFileSize parameter
- Log file auto growth using -Loggrowth parameter
|
1 |
> New-DbaDatabase -Name SQLTemp1 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 |
Let’s open database properties in SSMS and verify the conditions we specified.
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.
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.
|
1 |
> New-DbaDatabase -Name SQLTemp1 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -SecondaryFileCount 3 |
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.
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.
Let’s create another database by specifying the parameter -DefaultFileGroup to configure secondary filegroup as default.
|
1 |
>New-DbaDatabase -Name SQLTemp11 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary |
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.
|
1 |
>New-DbaDatabase -Name SQLTemp11 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary -Collation Latin1_General_CS_AI |
Let’s verify it from database properties as well. You can see database collation in database properties under the general page.
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.
|
1 |
>New-DbaDatabase -Name SQLTemp11 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -DefaultFileGroup Secondary -Collation Latin1_General_CS_AI -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.
If we verify it in the database properties, it shows the correct database owner as specified in DBATools command.
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.
|
1 |
>New-DbaDatabase -Name SQLTemp11 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -PrimaryFileMaxSize 100 -SecondaryFileMaxSize 100 |
In the following screenshot, we can see that auto growth is set to 100 MB for all data files.
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.
|
1 |
>New-DbaDatabase -Name SQLTemp11 -SqlInstance kashish\SQL2019CTP -PrimaryFileGrowth 40 -SecondaryFileCount 3 -SecondaryFileSize 30 -SecondaryFileGrowth 10 -Loggrowth 30 -PrimaryFileMaxSize 100 -SecondaryFileMaxSize 100 -confirm |
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023