Using sqlcmd from Powershell


I’ve been doing some Sharepoint 2010 stuff lately and one of the tasks I had to accomplish was to automate creating some databases using Powershell. Never having used Powershell before I was somewhat intrigued by the task, but it proved to be a little more hassle than I expected, and included voting up a bug in the sqlcmds “-Variable”-setting!! Check the details here: connect.microsoft.com/sqlserver/…/

What I basically needed to do was to create a database using Powershell, sqlcmd and a .sql script-file that accepted parameters. Sounds easy enough? Well…I might not me the smartest guy in the world but I didn’t expect to spend as much time as I did on it (the bug in invoke-sqlcmd taking most of the time to find out). Here’s what I ended up with:

The powershell script create_database.ps1

#Parameters that must be set:
$DBServer = "192.168.1.10"
$DBDataPath = "D:\MSSQL\Data\"
$DBLogPath = "E:\MSSQL\Log\"
$DBScriptFile = "c:\create databases.sql"

#Add snap-ins and create parameters in the correct format
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
$DBParam1 = "DBDataPath=" + $DBDataPath
$DBParam2 = "DBLogPath=" + $DBLogPath
$DBParams = $DBParam1, $DBParam2
Invoke-Sqlcmd -InputFile $DBScriptFile -Variable $DBParams -Serverinstance $DBServer -E

And here is the file create_databases.sql

CREATE
DATABASE [SharePoint_Config] ON PRIMARY
(NAME = N'SharePoint_Config', FILENAME = N'$(DBDataPath)SharePoint_Config.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% )
LOG ON
(NAME = N'SharePoint_Config_log', FILENAME = N'$(DBLogPath)SharePoint_Config.ldf' , SIZE = 100MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS_KS_WS
GO

Create as many parameters as you want, just keep in mind not to have spaces around the powershell parameters when defining them!!

This works:
$DBParam1 = “DBDataPath=” + $DBDataPath

This will not work:
$DBParam1 = “DBDataPath = ” + $DBDataPath