THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Create Database from PowerShell

Last week I presented a session on Using SMO to Manage SQL Server at SQL Connections in Orlando, Florida. This was the third major conference I've presented this session, and each time I do this session I change more demos from VB.Net to PowerShell. This time it was the demo to create a new database.

The big thing about my demos is that I want to reflect what I actually do in my job as a DBA, and I now use this script whenever I want to create a new database. The demo files I included with my presentation contained the VB.Net code I used to use so I wanted to make the code available here.

The first thing we need to do is load the SMO object library and connect to the server.

#createdb.ps1
#Creates a new database using our specifications
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer/MyInstance'

The next thing we'll do is set a string variable to the name of the database. This value can also be supplied as an argument to the script if you want. Then we'll instantiate the database object and add filegroups for PRIMARY (required for SQL Server), and another filegroup I call AppFG for the application data. I've found that I improve performance and recoverability by putting only the database metadata in the PRIMARY filegroup, setting its size to 5MB without expansion, then setting the AppFG (application filegroup) to be the default filegroup.

$dbname = 'SMO_DB'

# Instantiate the database object and add the filegroups
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, $dbname)
$sysfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'PRIMARY')
$db.FileGroups.Add($sysfg)
$appfg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, 'AppFG')
$db.FileGroups.Add($appfg)

Once the filegroups have been created, we can create the files for the database. First we create the file for the database metadata. I've set the size to be 5MB with no growth. To create the database the PRIMARY filegroup has to be set to be the default, so we'll set that here as well.

# Create the file for the system tables
$syslogname = $dbname + '_SysData'
$dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($sysfg, $syslogname)
$sysfg.Files.Add($dbdsysfile)
$dbdsysfile.FileName = $s.Information.MasterDBPath + '\' + $syslogname + '.mdf'
$dbdsysfile.Size = [double](5.0 * 1024.0)
$dbdsysfile.GrowthType = 'None'
$dbdsysfile.IsPrimaryFile = 'True'

Next we'll create the file to hold the application tables. Normally 25MB works for my databases, so I've set that in the Size parameter, and I use a growth parameter of 25%, because 10% is too small an increment when growth is required, in my opinion. I've also set a maximum size for this file of 100MB. I have to watch this to make sure we don't run out of space, but this is rarely a problem in my environment, and this max helps prevent me from running out of physical disk. (Note that sizes are specified in KB units, so I "do the math" in the script so it's easier to read.)

# Create the file for the Application tables
$applogname = $dbname + '_AppData'
$dbdappfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($appfg, $applogname)
$appfg.Files.Add($dbdappfile)
$dbdappfile.FileName = $s.Information.MasterDBPath + '\' + $applogname + '.ndf'
$dbdappfile.Size = [double](25.0 * 1024.0)
$dbdappfile.GrowthType = 'Percent'
$dbdappfile.Growth = 25.0
$dbdappfile.MaxSize = [double](100.0 * 1024.0)

Now I can create the file for the transaction log. I set this to an initial size of 10MB with 25% growth.

# Create the file for the log
$loglogname = $dbname + '_Log'
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, $loglogname)
$db.LogFiles.Add($dblfile)
$dblfile.FileName = $s.Information.MasterDBLogPath + '\' + $loglogname + '.ldf'
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = 'Percent'
$dblfile.Growth = 25.0

We can create the database now, and once it's been created we can grab the AppFG filegroup, set it's default property to True, alter the filegroup and alter the database. Now it's ready for loading the tables and other objects necessary for the application to work properly.

# Create the database
$db.Create()

# Set the default filegroup to AppFG
$appfg = $db.FileGroups['AppFG']
$appfg.IsDefault = $true
$appfg.Alter()
$db.Alter()

This script allows me to quickly create a new application database without the tedium of clicking through the GUI dialogs, but I can run this script against any of my servers without changing it to reference the specific database file locations, because I pull that from the server's Information collection, so it's cleaner than using Transact-SQL scripts as well. (Note, in this case I'm using the MasterDBPath and MasterDBLogPath properties from the Information collection.)

Allen

Published Monday, April 28, 2008 11:13 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Eric said:

ireceibe this error:

Property 'IsDefault' cannot be found on this object; make sure it exists and is settable.

At C:\PowerSQL\createdb.ps1:89 char:8

+ $appfg.I <<<< sDefault = $false

You cannot call a method on a null-valued expression.

At C:\PowerSQL\createdb.ps1:90 char:13

+ $appfg.Alter( <<<< )

August 27, 2008 10:12 AM
 

Jens Gyldenkærne Clausen said:

Thanks for a great article. I have been looking for something like this to make scriptbased creation of databases independent of hardcoded paths.

But would it be possible to grab the actual default database data/log location instead of just assuming that the files from the master db are in the right place?

December 2, 2009 11:24 AM
 

AllenMWhite said:

Thanks, Jens. I blogged about getting the default data/log locations here: http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx

December 2, 2009 5:16 PM
 

T-SQL Tuesday #15 Automation in SQL Server « Robert's SQL Server blog said:

February 8, 2011 5:13 AM
 

Kirpal Singh said:

Hi Allen,

I want to deploy SQL DB which is already uploaded in SCVMM 2012 library.

What will be the script syntex and what will the parameters.

Thanks

Kirpal Singh

June 26, 2012 4:06 AM
 

Shane said:

I'm new to powershell and trying to create a script that would allow me to create a database and have a user input the database name when executing the script. Can this be done as well?

February 20, 2013 10:27 AM
 

steve sofar said:

Hello

Very interesting code

I'm newby in Powershell

Is there a way to define a list of SQL Servers in a txt file eg : SQL-Servers.txt  and specifying the SQLServer the SQL authentication like following And to loop into it

to then execute the powershell script in your first example

SERVER1 sa only4admin

SERVER2 sa Power

SERVER3 test dbatest

thanks a lot

March 12, 2013 1:58 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement