Microsoft® SQL Server® 2012 Bible

789

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

LISTING 30-3 CreateDB.ps1

#createdb.ps1

#Creates a new database using our specifications

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

| out-null

# Instantiate a new SMO Server object and connect to server SQLTWSS\INST01

$s =

new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTWSS\INST01'

$dbname = 'SMO_DB'

# Set the database logical and physical names

$syslogname = $dbname + '_SysData'

$applogname = $dbname + '_AppData'

$loglogname = $dbname + '_Log'

# An SMO bug in SQL 2005 and SQL 2008 cause the default locations to

possibly be null

$fileloc = $s.Settings.DefaultFile

$logloc = $s.Settings.DefaultLog

if ($fileloc.Length = 0) {

$fileloc = $s.Information.MasterDBPath

}

if ($logloc.Length = 0) {

$logloc = $s.Information.MasterDBLogPath

}

# Place the files in the same location as the master database

$dbsysfile = $fileloc + '\' + $syslogname + '.mdf'

$dbappfile = $fileloc + '\' + $applogname + '.ndf'

$dblogfile = $logloc + '\' + $loglogname + '.ldf'

# 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)

# Create the file for the system tables

$dbdsysfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile')

($sysfg, $syslogname)

$sysfg.Files.Add($dbdsysfile)

$dbdsysfile.FileName = $dbsysfile

$dbdsysfile.Size = [double](5.0 * 1024.0)

Continues

c30.indd 789c30.indd 789 7/31/2012 9:46:23 AM7/31/2012 9:46:23 AM

http://www.it-ebooks.info