Allen White

Using SMO with PowerShell to create a database

Last month I presented a session on using PowerShell with SQL Server. One of the complaints I've heard as I've discussed the benefits of using SMO to build management solutions was that DBA's aren't programmers, and don't want to write programs. (Personally, I think script programming is no different from a skills perspective than VB or C# programming, but that's just my opinion.)

PowerShell 1.0 is now available and it uses the .Net 2.0 Framework, so all the capabilities of SMO are available in PowerShell. You have to load the SMO assembly at the start of the script (or put this in your profile script, if you're so inclined), but that's fairly easy to do.

Dan Sullivan has created kind of an add-on to PowerShell he calls PowerSMO which gives you a lot of flexibility in using SMO within PowerShell. I wanted to present SMO directly, and let the reader decide how to best make use of the tools.

In my presentation I showed a few script examples using SMO in PowerShell, the first of which is presented here. It's essentially the same script I've done in VB.Net, but converted to PowerShell, to create a new database with the properties I specify, rather than taking the SQL Server defaults.

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

#Instantiate objects for the Server, the Database and the FileGroup
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer'
$db = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($s, "NewDB")
$fg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, "PRIMARY")
#Add the filegroup to the database
$db.FileGroups.Add($fg)

#Instantiate the data file object and add it to the PRIMARY filegroup
$dbdfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($fg, "NewDB_Data")
$fg.Files.Add($dbdfile)
#Set the properties of the data file
$dbdfile.FileName = "E:\MSSQL\Data\NewDB_Data.mdf"
$dbdfile.Size = [double](25.0 * 1024.0)
$dbdfile.GrowthType = "Percent"
$dbdfile.Growth = 25.0
$dbdfile.MaxSize = [double](100.0 * 1024.0)

#Instantiate the log file object and set its properties
$dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($db, "NewDB_Log")
$dblfile.FileName = "L:\MSSQL\Data\NewDB_Log.ldf"
$dblfile.Size = [double](10.0 * 1024.0)
$dblfile.GrowthType = "Percent"
$dblfile.Growth = 25.0

#Create the database
$db.Create()

In another post I'll demonstrate using PowerShell for regular data access.

Allen

Published again!

Wow, it's been a while since I've blogged, but it's been a REALLY busy six weeks!

Early in March I presented a session at the Ohio North SQL Server User's Group meeting on PowerShell and SQL Server. I'll follow up with the sample SMO scripts I built for the presentation in the next couple of days.

Last week I taught a Business Intelligence (BI) Bootcamp at my company. I covered three three-day classes, one on Reporting Services, one in Integration Services and one on Analysis Services, in one week. The preparation was incredible, and the eight people in my class (it was an internal class not open to the public) told me they got a lot out of it. I was exhausted at the end of it, though.

Late in the week I recieved an email from Tony Davis (of the Simple Talk newsletter) that my first of three articles on using SMO to generate SQL Server maintenance jobs was published to their site. It can be accessed at http://www.simple-talk.com/sql/backup-and-recovery/automate-your-database-maintenance-using-smo/.

Also, as Will Parker sings in Oklahoma!:
I got to Kansas City on a Friday,
By Saturday I l'arned a thing or two.

I ran the Olathe Marathon on Saturday, March 31. I finished. It wasn't pretty, but I finished. I'd also like to thank Rick Heiges for putting together the March Madness pool. I did really well in the early going, and would have won had my Buckeyes hit about half the three-pointers they shot!

Examples of using SMO from PowerShell tomorrow.

Allen